Programmer's Reference

In this tutorial I demonstrate 3 key things. 

1. Creating a DSN to connect to any database
2. Connecting to a database with SAS
3. Using PROC SQL to create a SAS dataset

The example in the tutorial uses a DSN to connect to an MS Access database.  You should know that you can connect to any database as long as you have set your DSN up correctly.  If you’re connection does not work, contact a systems support rep so that he/she can set up the parameters correctly for you, or you can use one of your functioning DSNs.

Also, the file used in this example is available for download.

If you are running SAS PC (or any form of SAS) you will likely have a table that you want to make a copy of.  Perhaps with your new copy you will want to exclude certain records or add new fields.  Below are some examples that will help you achieve these tasks.

It is good to remember that with in SAS, your default library is Work.  So any new datasets / tables that you create through your program will go to work by default unless you prefix your dataset name / table name with the name of the library you wish to operate in.

Making a copy of an existing dataset in SAS

Example (assuming the dataset is in your work lib and want a copy of it in your work lib):

DATA existingDataSet;
SET copyOfDataSet;
RUN;

As mentioned before, by default the copy of the existing data set will be placed in your work library. 

Example 2 (take existing dataset (ds) from work library and place in lib DataMine):

DATA existingDataSet;
SET DataMine.copyOfDataSet;
RUN;

Now your the new table, copyOfDataSet, will be for use in your library DataMine. 

Filtering the records in your new data set (i.e. setting parameters).

What if you wanted to set some parameters?  Let’s say for example that you only wanted records with rec_id greater than 100 (assuming you have the field rec_id in your data set.

DATA existingDataSet;
SET DataMine.copyOfDataSet(WHERE=(rec_id GE 100));
RUN;

The new data set resulting data set "copyOfDataSet" will now be created will all records that have a rec_id greater than or equal to 100 in your library DataMine.  To create it in your work librarly, simply replace DataMine with the word "Work" or delete the library reference completely.

Adding a new field/variable to your new SAS dataset/table

Let’s go a step further and add a variable to our new sas dataset

DATA existingDataSet;
SET copyOfDataSet(WHERE=(rec_id GE 100));
length newTextVar $50;
RUN;

Our new dataset now consists of records with rec_id greater than or equal to 100 and it has a new text field named newTextVar with 50 characters of available space.  The max I believe is 5000 characters.

That concludes the the mini lesson in creating copies of existing SAS datasets/tables. 

In this video tutorial, you will learn how to create a new library in SAS.  Depending on your license, there are variety of liraries that can be created in SAS.  These libraries can write your datasets to a variety of files and databases.  This example illustrates how to create another library with the Default Engine type which is SAS.

See attached swf file for video tutorial.  Right click and open in new window to view the tutorial.

To export the data set you created in SAS to excel simply write the following:

PROC EXPORT DATA= LIBRARY.DATA_SET

            OUTFILE= "DIRECTORY"

            DBMS=EXCEL REPLACE;

     SHEET=" WORK_SHEET_NAME";

RUN;

1. In first line above, we are specifying which data set to get by specifying the library and the data set name.
2. The second line is where we will specify our output location.
3. The third line has the word REPLACE. If we want to replace our previous output with our new one every time we execute the statement, leave this here.  
4. The fourth line SHEET=".." will contain the name of the sheet.  If you don’t want to replace the work sheet every time you run the program and you want to archive your old results, you can always change your SHEET name every time you run the program.

Actual Example:

PROC EXPORT DATA= WORK.SALES_FIG

            OUTFILE= "C:\SAS\SALES\DATA_OUT"

            DBMS=EXCEL REPLACE;

     SHEET="SALES_SUMMARY";

RUN;