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.
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.
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
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;
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??
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.