Transpose using SAS array

[PRAVIN_JAIN]

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;

[Ksharp · Accepted solution]

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;

[Astounding]

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;