Dear,
I need to populate IMPDAY variable value for records having flag='Y'. The first record with flag='Y' after last record where flag='N' then set impday=lday. for all records until another record with flag='N'
data one;
input id flag $ lday;
datalines;
1 N 1
1 N 3
1 Y 4
1 Y 6
1 Y 10
1 N 12
1 Y 15
1 N 20
2 Y 10
2 Y 15
3 Y 20
;
OUTPUT NEEDED
ID FLAG LDAY IMPDAY
1 N 1
1 N 3
1 Y 4 4
1 Y 6 4
1 Y 10 4
1 N 12
1 Y 15 15
1 N 20
2 Y 10 10
2 Y 15 10
3 Y 20 20
For OBS 3,4,5, the impday=4 because obs=3 is first lday after flag=N. For obs=7, the impday=15, because it s first day after flag='N'.
For id in 2 and 3, if there are no obs with flag='N' then i need to populate first record value as IMPDAY. Please suggest. Thank you
Please edit your original message in this thread to provide a meaningful subject line for your question, one that describes the question itself.
data one;
input id flag $ lday;
datalines;
1 N 1
1 N 3
1 Y 4
1 Y 6
1 Y 10
1 N 12
1 Y 15
1 N 20
2 Y 10
2 Y 15
3 Y 20
;;;
run;
proc sort; by id lday;
run;
data want;
set one;
by id;
retain impday .;
if first.id then impday=.;
if flag='Y' and missing(impday) then impday=lday;
if flag='N' then impday=.;
run;
If I understand correctly then try next code:
data one;
input id flag $ lday;
datalines;
1 N 1
1 N 3
1 Y 4
1 Y 6
1 Y 10
1 N 12
1 Y 15
1 N 202 Y 102 Y 153 Y 20
;
run;
data want;
set one;
retain impday;
if flag= 'N' then impday = .; else
if impday = . then impday = lday;
run;
data one;
input id flag $ lday;
datalines;
1 N 1
1 N 3
1 Y 4
1 Y 6
1 Y 10
1 N 12
1 Y 15
1 N 20
2 Y 10
2 Y 15
3 Y 20
;;;
run;
data want;
set one;
by id flag notsorted;
retain impday;
if first.flag then call missing(impday);
if first.flag and flag='Y' then impday=lday;
run;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.