Dear everyone,
Can I please ask how can I produce the desired output data (B) from input data (A)?
Your help is much appreciated.
Data A | Data B | ||||||
id | yr | val | id | yr | val | val2 | |
1 | 1 | 11 | 1 | 1 | 11 | 11 | |
1 | 2 | . | 1 | 2 | . | 12 | |
1 | 3 | . | 1 | 3 | . | 13 | |
1 | 4 | 44 | 1 | 4 | 44 | 44 | |
2 | 1 | . | 2 | 1 | . | 21 | |
2 | 2 | 22 | 2 | 2 | 22 | 22 | |
3 | 1 | . | 3 | 1 | . | 32 | |
3 | 2 | 33 | 3 | 2 | 33 | 33 | |
3 | 3 | . | 3 | 3 | . | 34 | |
3 | 4 | 44 | 3 | 4 | 44 | 44 | |
4 | 1 | 11 | 4 | 1 | 11 | 11 | |
4 | 2 | 22 | 4 | 2 | 22 | 22 | |
4 | 3 | 33 | 4 | 3 | 33 | 33 | |
4 | 4 | 44 | 4 | 4 | 44 | 44 | |
5 | 1 | 55 | 5 | 1 | 55 | 55 | |
5 | 2 | . | 5 | 2 | . | 56 | |
5 | 3 | . | 5 | 3 | . | 57 | |
5 | 4 | . | 5 | 4 | . | 58 | |
6 | 1 | . | 6 | 1 | . | 63 | |
6 | 2 | . | 6 | 2 | . | 64 | |
6 | 3 | . | 6 | 3 | . | 65 | |
6 | 4 | 66 | 6 | 4 | 66 | 66 |
proc sort data=have;
by id yr;
run;
data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;
proc sort data=int;
by id descending yr;
run;
data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;
proc sort data=want;
by id yr;
run;
proc sort data=have;
by id yr;
run;
data int;
set have;
by id;
retain val2;
if first.id then val2 = .;
if val ne . then val2 = val;
else if val2 ne . then val2 = val2 + 1;
run;
proc sort data=int;
by id descending yr;
run;
data want;
set int;
by id;
retain val3;
if val2 = . then val2 = val3;
else val3 = val2;
val3 = val3 - 1;
drop val3;
run;
proc sort data=want;
by id yr;
run;
data have;
input id yr val;
cards;
1 1 11
1 2 .
1 3 .
1 4 44
2 1 .
2 2 22
;
run;
data temp;
set have;
by id;
retain found;
if first.id then call missing(found);
if not missing(val) and not found then do;output;found=1;end;
run;
data want;
merge have temp(keep=id yr val rename=(yr=_yr val=_val));
by id;
if missing(val) then val=_val+yr-_yr;
drop _yr _val;
run;
Thank you very much KurtBremser and Kisharp.
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.