SAS Coding: 3 Data Set Tricks in PROC SQL

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.

Maybe you are like me and your SAS programs frequently bounce from PROC SQL to data steps depending on the task at hand. Some things you cannot do in PROC SQL as easily as the data step and vice versa.  Here’s 3 data set tricks that I have successfully used with PROC SQL.  These are some time savers that you can use in your work.  However, if you know some others add them in the Comments section – I double-dare you.

 

Trick #1: Drop and Keep Options

You can use the DROP and KEEP options on the FROM or CREATE statements. This is handy when you want to exclude or include a list of variables.  Consider this example where I saved myself some typing because I know the only variable I don’t want is country. Otherwise I would have had to type out all of the other variables just to exclude a single one.

proc sql;
 create table Newtable  as
    select *
 from sashelp.prdsal2(drop=country);
quit; 

Trick #2: Limit the Observations

OBS allows you to control the number of observations input to the procedure.  I would use this if I had a particularly large data table and I just needed to test or see what was happening.

proc sql;
 create table Newtable as
    select *
 from sashelp.prdsal2(obs=5);
quit; 

PROC SQL also has a OUTOBS and INOBS options that would work as easily.

Trick #3: Renaming Variables 

You can rename a variable as you input or output the data.  This is useful if you just have a few variables that need a new name and you want to avoid typing all the variables.  Here’s how it works:

proc sql;
 create table Newtable (rename=(country=CTRY)) as
    select *
 from sashelp.prdsal2;
quit;

More Information

Many of the data set options can be used in PROC SQL.  SAS Support site lists all of the data set options.  Here’s a great SAS Global Forum Using Data Set Options in PROC SQL paper that goes into more detail. Lots of good things come from the SAS Global Forum!

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.