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