BookmarkSubscribeRSS Feed
WendyT
Pyrite | Level 9
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.
3 REPLIES 3
DanielSantos
Barite | Level 11
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
WendyT
Pyrite | Level 9
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 615 views
  • 0 likes
  • 3 in conversation