11-18-2016 03:02 AM
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.
11-18-2016 03:13 AM
11-18-2016 07:51 AM
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??
11-18-2016 07:57 AM - edited 11-18-2016 07:58 AM
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.
11-18-2016 04:17 AM
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:
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.