BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Coding & Data, Enterprise Guide

SAS Coding: 3 Data Set Tricks in PROC SQL

Submitted by on 2012-04-12 – 7:15 AM 8 Comments

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.

Credit: http://www.unprofound.com

 

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!

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , ,

8 Comments »

  • >> I send it to them anyway to demonstrate the flexibility of SAS!

    Ha! Ha! I do the same thing.

  • Chris S. says:

    I’ve used SQL with these options to avoid writing out long SELECT statements just to avoid merging two columns used in the join, like so:

    proc sql;
    create table X(drop=patient_b) as
    select distinct *
    from patient a
    left join encounter(rename=(patient=patient_b)) b
    on a.patient=b.patient_b
    ;
    quit;

    That avoids a lot of extra code. Unfortunately, if you need to share your SQL with non-SAS developers (e.g., ETL is done in a different tool), then it’s not so easy to share like this. Then again, I send it to them anyway to demonstrate the flexibility of SAS!

  • Good point Gerry. See this is what happens when you write your heading “3 Tricks” and then go find content. 😉

  • Gerry says:

    For tip #2, I would recommend using the (obs=#) data set option over proc sql’s inobs/outobs because SAS prints a warning to the log when the latter is used (“early termination”).

  • I’ve used these a lot! Let us know if you discover some good ones.

  • Divyesh Dave says:

    Your tips will come in handy in my “Proc SQL” development !

  • Thank Michel!
    Let me know if you want to share some ETL tricks!

  • michel jubinville says:

    Great. I always copy and paste the best tricks I can find and your name appears from time to time. BTW, I’m beginning the reading of “Building BI using SAS”. I’m mostly a ETL SAS programmer who wants to make the next step, eventually trying myself at a BI certif if such a thing exist. Thanks for making things easier !
    Have a nice day.