DATA Step, Macro, Functions and more

USING RETAIN STATEMENT IN PROC SQL

Reply
Occasional Contributor
Posts: 15

USING RETAIN STATEMENT IN PROC SQL

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
Super User
Posts: 19,822

Re: USING RETAIN STATEMENT IN PROC SQL

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. 

Super User
Posts: 7,822

Re: USING RETAIN STATEMENT IN PROC SQL

If you had taken the time to read the documentation, you would not have posted this question.

Quote from there:

Valid: in a DATA step

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 15

Re: USING RETAIN STATEMENT IN PROC SQL

Posted in reply to KurtBremser
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
Super User
Posts: 7,822

Re: USING RETAIN STATEMENT IN PROC SQL

[ Edited ]
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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,971

Re: USING RETAIN STATEMENT IN PROC SQL

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.

Ask a Question
Discussion stats
  • 5 replies
  • 1527 views
  • 0 likes
  • 4 in conversation