DATA Step, Macro, Functions and more

Transpose using SAS array

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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:

IdValuedate
111101-Jan-17
1110131-Jan-17
111203-Feb-17
122003-Feb-17
122109-Feb-17
132221-Jul-17

 

 

Output:

 

Iddate1 Value1 date2 value2
111-Jan-1711031-Jan-17101
123-Feb-172009-Feb-17210
131-Jul-17222..

 

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,618

Re: Transpose using SAS array

Posted in reply to PRAVIN_JAIN
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;

View solution in original post


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

Re: Transpose using SAS array

Posted in reply to PRAVIN_JAIN
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,542

Re: Transpose using SAS array

[ Edited ]
Posted in reply to PRAVIN_JAIN

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 117 views
  • 0 likes
  • 3 in conversation