Hi All,
I have a SAS dataset structured as :
Var1 | Var2 | Var3 |
---|---|---|
A | . | . |
A | . | . |
A | . | . |
A | 1 | 16APR2010 |
A | . | . |
B | . | . |
B | 1 | 17MAY2010 |
B | . | . |
B | . | . |
I want to create macro variables : first which counts the distinct number of values in Var1 (2 in this case - A &B) and for each value create a process which reads the data ONLY until Var2 = 1 for each value. Once this is done, then I would like to populate my VAR3 backwards, for example in above case it would be 16APR2010 for A and 17 May2010 for B so in total there should be 6 records in final output file.
Please help.
Kind regards
SK
data have;
input var1 $ var2 var3 :date9.;
format var3 date9.;
cards;
A . .
A . .
A . .
A 1 16APR2010
A . .
B . .
B 1 17MAY2010
B . .
B . .
;
run;
/*Distinct number of values in var1*/
proc sql;
select count(distinct var1) into: num from have;
quit;
%put #
data want;
do i=1 by 1 until(last.var1);
set have;
by var1;
retain count;
if var2=1 then count=i;
end;
do i=1 by 1 until (last.var1);
set have;
by var1;
if i<=count then output;
end;
format var3 date9.;
drop i count;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.