Hi,
I have a data set like this
SUBJID PSADN days flg
70433 11SEP2009 -10 1
70433 21SEP2009 1 1
70433 12OCT2009 22 1
70433 30OCT2009 40 1
70433 01DEC2009 72 1
70433 21DEC2009 92 1
70433 11JAN2010 113 1
70433 01FEB2010 134 1
70433 22FEB2010 155 1
70433 16MAR2010 177 1
i need to create another varaible satisfying this condition
if flg eq 1 & days>84.
then need to select the first data. in this case 21DEC2009 . need to retain this to new varaible
desired output is
SUBJID PSADN days flg newvar
70433 11SEP2009 -10 1 21DEC2009
70433 21SEP2009 1 1 21DEC2009
70433 12OCT2009 22 1 21DEC2009
70433 30OCT2009 40 1 21DEC2009
70433 01DEC2009 72 1 21DEC2009
70433 21DEC2009 92 1 21DEC2009
70433 11JAN2010 113 1 21DEC2009
70433 01FEB2010 134 1 21DEC2009
70433 22FEB2010 155 1 21DEC2009
70433 16MAR2010 177 1 21DEC2009
Thanks in Advance!!!!!!!
Sam
Here is one way you can do it:
data want (drop=gotit);
format newvar date.;
do until(last.subjid);
set have;
by subjid;
if days gt 84 and flg eq 1 and gotit ne 1 then do;
newvar=psadn;
gotit=1;
end;
end;
do until(last.subjid);
set have;
by subjid;
output;
end;
run;
hi ... here's one idea, self-interleave (I added a 2nd SUBJID) ...
data x;
input subjid :$5. psadn :date9. days flg @@;
format psadn date9.;
datalines;
70433 11SEP2009 -10 1 70433 21SEP2009 1 1
70433 12OCT2009 22 1 70433 30OCT2009 40 1
70433 01DEC2009 72 1 70433 21DEC2009 92 1
70433 11JAN2010 113 1 70433 01FEB2010 134 1
70433 22FEB2010 155 1 70433 16MAR2010 177 1
80433 11SEP2009 -10 1 80433 21SEP2009 1 1
80433 12OCT2009 22 1 80433 11JAN2010 113 0
80433 01FEB2010 134 0 80433 22FEB2010 155 1
80433 16MAR2010 177 1
;
proc sort data=x;
by subjid days;
run;
data y;
do until (last.subjid);
set x (in=one) x;
by subjid;
newvar = ifn(one and missing(newvar) and flg*days gt 84, psadn , newvar);
if ^one then output;
end;
format newvar date9.;
run;
partial output ...
subjid psadn days flg newvar
70433 11SEP2009 -10 1 21DEC2009
70433 21SEP2009 1 1 21DEC2009
<more>
80433 11SEP2009 -10 1 22FEB2010
80433 21SEP2009 1 1 22FEB2010
<more>
ps "Interleaving a Dataset with Itself: How and Why" by Howard Schreier
Thank you so much Art and mike for valuable suggestions.
my o/p is little bit different which i stated before
i need my o/p like this. Sorry for the confusion!!!!
SUBJID PSADN days flg newvar
70433 11SEP2009 -10 1 .
70433 21SEP2009 1 1 .
70433 12OCT2009 22 1 .
70433 30OCT2009 40 1 .
70433 01DEC2009 72 1 .
70433 21DEC2009 92 1 21DEC2009
70433 11JAN2010 113 1 21DEC2009
70433 01FEB2010 134 1 21DEC2009
70433 22FEB2010 155 1 21DEC2009
70433 16MAR2010 177 1 21DEC2009
hi ... that's easier ...
data y;
do until (last.subjid);
set x;
by subjid;
newvar = ifn(missing(newvar) and flg*days gt 84, psadn , newvar);
output;
end;
format newvar date9.;
run;
That is even easier:
data want (drop=gotit);
set have;
format newvar date.;
retain gotit newvar;
by subjid;
if first.subjid then do;
call missing(newvar);
call missing(gotit);
end;
if days gt 84 and flg eq 1 and gotit ne 1 then do;
newvar=psadn;
gotit=1;
end;
run;
Hi Art,
I made your code shorter.:smileysilly:
data want (drop=gotit);
set have;
format newvar date.;
retain gotit newvar;
by subjid;
if first.subjid then call missing(newvar,gotit);
if days gt 84 and flg eq 1 and gotit ne 1 then do;
newvar=psadn;
gotit=1;
end;
run;
Thank you all, Art, Mike and linlin!!!!!!
I am learning so many thing through this forum and sas experts like you all
THanks
Sam
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.