DATA Step, Macro, Functions and more

Transpose the data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Transpose the data

Hi frnds...
I have a problem that could be a simple one.
In a dataset I have two variables id and spend
like
data a;
input id spend;
cards;
1 100
1 200
1 300
2 150
2 690
;
run;

I want output like

id jan feb mar
1 100 200 300
2 150 690 .

through arrays or sql  share ur simplest code ..thanks in advance


Accepted Solutions
Solution
‎12-21-2014 08:25 AM
Super User
Posts: 9,691

Re: Transpose the data

How about:

data have;
input id spend;
cards;
1 100
1 200
1 300
2 150
2 690
;
run;
data want;
 set have;
 by id;
 array x{*} jan feb mar;
 retain jan feb mar     ;
 if first.id then do;n=0;call missing(of x{*});end;
 n+1;
 x{n}=spend;
 if last.id;
 drop n spend;
run;


Xia Keshan

View solution in original post


All Replies
Solution
‎12-21-2014 08:25 AM
Super User
Posts: 9,691

Re: Transpose the data

How about:

data have;
input id spend;
cards;
1 100
1 200
1 300
2 150
2 690
;
run;
data want;
 set have;
 by id;
 array x{*} jan feb mar;
 retain jan feb mar     ;
 if first.id then do;n=0;call missing(of x{*});end;
 n+1;
 x{n}=spend;
 if last.id;
 drop n spend;
run;


Xia Keshan

Frequent Contributor
Posts: 115

Re: Transpose the data

Same as Xia:

data have;

input id $ spend;

cards;

1 100

1 200

1 300

2 150

2 690

;

data want(drop=count spend);

set have ;

by id;

array mon(3) jan feb march;

retain mon;

if first.id then do;

call missing(of mon{*});

       count=0;

            end;

           count+1;

mon(count)= spend;

if last.id then output;

run;

Thanks,

Naveen Srinivasan

L&T infotech

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 195 views
  • 3 likes
  • 3 in conversation