topic Re: Data step IF/THEN value change in SQL? in SAS Procedures

Data step IF/THEN value change in SQL?

WendyT — Mon, 26 Oct 2009 15:23:38 GMT

Currently, I have a datastep that accesses a very large table, and it must later be sorted by three variables, which takes forever.

DATA SAMPLES_DATA ;
SET DATABASE.SAMPLES (RENAME=(SMPL_ID=SAMP_NUM STN_NM=STATION ANLYT_NM=ANALYTE)) ;
IF PROJ_NM ='Project 1 old name' then PROJ_NM =’Project 1 new name' ;
RUN ;
…later on…
PROC SORT DATA=DATABASE.SAMPLES ; BY SAMP_NUM ANALYTE STATION ; RUN ;

I’m trying to come up with a solution using SQL, so I can use an ORDER BY that runs much faster.

PROC SQL NOPRINT ;
CREATE TABLE SAMPLES_SQL
AS SELECT *,
SMPL_ID AS SAMP_NUM,
STN_NM AS STATION,
ANLYT_NM AS ANALYTE
FROM DATABASE.SAMPLES
ORDER BY SAMP_NUM, ANALYTE, STATION ;
QUIT;

However, I’m stumped as how to change the name of Project 1 in PROC SQL. I played with the CASE statement, but if I understand the documentation correctly, all values of PROJ_NM have to be represented. There are lots of projects, with new ones added on a fairly frequent basis, so that’s not a very good solution.

Thanks for any advice you can give me.

Wendy T.

Re: Data step IF/THEN value change in SQL?

DanielSantos — Mon, 26 Oct 2009 15:39:22 GMT

Hi

The CASE statement works exactly as the IF statement.

IF PROJ_NM ='Project 1 old name' then PROJ_NM =’Project 1 new name' ;

Would translate to:

CASE WHEN PROJ_NM ='Project 1 old name' THEN ’Project 1 new name' ELSE PROJ_NM END AS PROJ_NM

Check the online doc here:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473682.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Data step IF/THEN value change in SQL?

WendyT — Mon, 26 Oct 2009 16:37:38 GMT

Hi Daniel-

That's where I was in the documentation, but it was unclear how to specify no change.

Thanks so much for your help!

Wendy

Re: Data step IF/THEN value change in SQL?

sbb — Mon, 26 Oct 2009 16:51:51 GMT

You may want to consider using a SAS PROC FORMAT to maintain the changes to your variable. The PUT function would be used with your SELECT and SAS variable specification.

Scott Barry
SBBWorks, Inc.