topic Re: USING RETAIN STATEMENT IN PROC SQL in SAS Programming

USING RETAIN STATEMENT IN PROC SQL

GeethaMN — Fri, 18 Nov 2016 07:57:52 GMT

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.

Re: USING RETAIN STATEMENT IN PROC SQL

Reeza — Fri, 18 Nov 2016 08:02:06 GMT

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.

Re: USING RETAIN STATEMENT IN PROC SQL

Kurt_Bremser — Fri, 18 Nov 2016 08:13:42 GMT

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

Re: USING RETAIN STATEMENT IN PROC SQL

RW9 — Fri, 18 Nov 2016 09:17:59 GMT

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.

Re: USING RETAIN STATEMENT IN PROC SQL

GeethaMN — Fri, 18 Nov 2016 12:51:27 GMT

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??

Re: USING RETAIN STATEMENT IN PROC SQL

Kurt_Bremser — Fri, 18 Nov 2016 12:58:34 GMT

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.