BookmarkSubscribeRSS Feed
GeethaMN
Obsidian | Level 7

Hello Everyone,

 

Could any one help me out how to retain a variable in Proc SQL like how we do in Base SAS. 

 

syntax please....

 

 

Thanks,

Geetha.

SAAAS
5 REPLIES 5
Reeza
Super User

You can't use retain or anything similar in PROC SQL AFAIK. 

 

Please explain what you're trying to do in detail and maybe we can offer alternate solutions. 

GeethaMN
Obsidian | Level 7
proc sql;
	create table VS_der as 
		select *, catx('-',STUDYID,SUBJID,SITEID) as USUBJID,
		       (case
               when upcase(visit) ne 'UNSCHEDULED' then VISITN
			   else VISITN+0.1
			   end) as AVISITN
			from vs;
quit;

visitinfo.png

I agree with you Kurt it is valid for only data step. but when am doing the above step, wherever the visit ne 'unscheduled' i want to have avisitn value should be previous value+0.1.

 

for ex., at 7th observation the value should not be 999.1 it should be 6.1 and the same way at 15th and 16th observation it should be 5.1,5.2.. that is my requirement.

 

so, i thought of is there any chance of doing retaining avisitn variable in proc sql..  actually in sql we can able to retain a variable by declare .. but how we can do in proc sql??

SAAAS
Kurt_Bremser
Super User
data vs_der;
set vs;
oldvisitn = lag(visitn);
if upcase(visit) = 'UNSCHEDULED' then visitn = oldvisitn + .1;
drop oldvisitn;
run;

Here the data step really comes into its own, as it provides functionality that is quite complicated to build in SQL.

 

Edit: changed dataset names.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Retain is irrelevant in terms of SQL.  Remember the two are different languages with their own ways of thinking.  When designing an SQL step, you select the variables you wish to keep, then specify from/how.  Do not fall into the trap of being a lazy programmer and doing:

select *

Take those extra few seconds to type in (or if your lucky enough to have a proper ide, copy/paste from object viewer) exactly what data you need.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 14058 views
  • 0 likes
  • 4 in conversation