## Transpose using SAS array

Solved
Occasional Contributor
Posts: 6

# Transpose using SAS array

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;

Accepted Solutions
Solution
‎12-04-2017 10:16 PM
Super User
Posts: 10,850

## Re: Transpose using SAS array

``````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;``````

All Replies
Solution
‎12-04-2017 10:16 PM
Super User
Posts: 10,850

## Re: Transpose using SAS array

``````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;``````
Super User
Posts: 6,935

## Re: Transpose using SAS array

[ Edited ]

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;

☑ This topic is solved.