Is there an easy way to create WANT column with data having fields ID, DATE and HAVE column without using lag -
ID | DATE | Have | Want |
A | 30-Jun-06 | 1 | 30-Jun-06 |
A | 31-Jul-06 | 1 | 30-Jun-06 |
A | 31-Aug-06 | 1 | 30-Jun-06 |
A | 30-Sep-06 | 1 | 30-Jun-06 |
A | 31-Oct-06 | 1 | 30-Jun-06 |
A | 30-Nov-06 | 0 | |
A | 31-Dec-06 | 0 | |
B | 30-Jun-06 | 0 | |
B | 31-Jul-06 | 0 | |
B | 31-Aug-06 | 0 | |
B | 30-Sep-06 | 0 | |
B | 31-Oct-06 | 1 | 31-Oct-06 |
B | 30-Nov-06 | 1 | 31-Oct-06 |
B | 31-Dec-06 | 1 | 31-Oct-06 |
C | 30-Jun-06 | 0 | |
C | 31-Jul-06 | 1 | 31-Jul-06 |
C | 31-Aug-06 | 1 | 31-Jul-06 |
C | 30-Sep-06 | 0 | |
C | 31-Oct-06 | 0 | |
C | 30-Nov-06 | 1 | 30-Nov-06 |
C | 31-Dec-06 | 1 | 30-Nov-06 |
Something like this may work (not tested):
data data_want;
set data_have;
by id have notsorted;
if first.have then do;
if have=1 then want=date;
else call missing(want);
end;
retain want;
run;
Something like this may work (not tested):
data data_want;
set data_have;
by id have notsorted;
if first.have then do;
if have=1 then want=date;
else call missing(want);
end;
retain want;
run;
Many thanks, that works!
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 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.