Hi,
I have valuation on different dates for given Id's. I want to have in output first two valuation by chronological order.
I used array but want to handle dimension dynamically. Also retain statement giving incorrect values for id having less no of rows.
Input:
Id | Value | date |
11 | 110 | 1-Jan-17 |
11 | 101 | 31-Jan-17 |
11 | 120 | 3-Feb-17 |
12 | 200 | 3-Feb-17 |
12 | 210 | 9-Feb-17 |
13 | 222 | 1-Jul-17 |
Output:
Id | date1 | Value1 | date2 | value2 |
11 | 1-Jan-17 | 110 | 31-Jan-17 | 101 |
12 | 3-Feb-17 | 200 | 9-Feb-17 | 210 |
13 | 1-Jul-17 | 222 | . | . |
code:
data t1;
input ID DATE VAL;
INFORMAT DATE DATE9.;
FORMAT DATE DATE9.;
DATALINES;
11 01JAN2017 101
11 31JAN2017 110
11 03FEB2017 230
12 03FEB2017 112
12 09FEB2017 114
13 01JUL2017 123
RUN;
PROC SORT DATA=T1;
BY ID DATE;
RUN;
data t2;
set t1;
by id;
/*retain m1 m2;*/
array amtt[*] amt1-amt3;
array datet[*] date1-date3;
if first.id then do;
i=0;
amtt[1]=VAL;
datet[1]=date;
end;
i+1;
amtt[i]=VAL;
datet[i]=date;
if last.id;
retain amt1 amt2 amt3 date1 date2 date3;
run;
data have;
input Id Value date $;
cards;
11 110 1-Jan-17
11 101 31-Jan-17
11 120 3-Feb-17
12 200 3-Feb-17
12 210 9-Feb-17
13 222 1-Jul-17
;
run;
proc sql;
select max(n) into : n
from (select count(*) as n from have group by id);
quit;
proc summary data=have;
by id;
output out=want idgroup(out[&n] (value date)=);
run;
data have;
input Id Value date $;
cards;
11 110 1-Jan-17
11 101 31-Jan-17
11 120 3-Feb-17
12 200 3-Feb-17
12 210 9-Feb-17
13 222 1-Jul-17
;
run;
proc sql;
select max(n) into : n
from (select count(*) as n from have group by id);
quit;
proc summary data=have;
by id;
output out=want idgroup(out[&n] (value date)=);
run;
What is supposed to be dynamic about this? The number of elements you decide to keep? If that's the case, here's a way. Assuming you have already read in the data and sorted by ID DATE:
%let n=2;
data want;
set have;
by id;
retain date1-date&n value1-value&n;
array dates {&n} date1-date&n;
array values {&n} value1-value&n;
if first.id then do _n_=1 to &n;
dates{_n_} = .;
values{_n_}=.;
recnum = 0;
end;
recnum + 1;
if recnum <= &n then do;
dates{recnum} = date;
values{recnum} = value;
end;
if last.id;
keep id date1-date&n value1-value&n;
format date: date9.;
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.