Transpose data

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Transpose data

Hi ,

 

I have a dataset like below

 

idcompanystoreprice
991.00001.00001.0000
321.0321.00001.045
491.9171.3061.3345

 

I need the output to be like below . Can anyone pls help

creditproductFactor
99company1.0000
32company1.032
49company1.917
99store1.0000
32store1.0000
49store1.306
99price1.0000
32price1.045
49price1.3345

 

 


Accepted Solutions
Solution
‎06-16-2016 09:53 PM
Trusted Advisor
Posts: 1,137

Re: Transpose data

Alternatively by arrays

 

data want;
length product $10.;
set have;
array cm(3) $10 ("company" "store" "price");
array cmsq(3) company store price;
do i = 1 to 3;
product=cm(i);
factor=cmsq(i);
output;
end;
keep id product factor;
run;
Thanks,
Jag

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: Transpose data

[ Edited ]

Please try the transpose procedure

 

data have;
input id	company	store	price;
cards;
99	1.0000	1.0000	1.0000
32	1.032	1.0000	1.045
49	1.917	1.306	1.3345
;

proc sort data=have;
by id;
run;

proc transpose data=have out=trans(rename=(col1=factor)) name=product;
by id;
var company	store	price;
run;
Thanks,
Jag
Contributor
Posts: 31

Re: Transpose data

Hi Jag,

 

I got the output but i have a question  when i have 100's of variables so i need to list all of them in var ?

 

or there an easy way to do it

Trusted Advisor
Posts: 1,137

Re: Transpose data

[ Edited ]

if you have more variables to transpose then you could create a macro variable and store all the variable that you need to tranpose.
something like below,

 

in the below example we are using the &var macro variable with all the variable names separated by space in the var statement.

 

data have;
input id	company	store	price;
cards;
99	1.0000	1.0000	1.0000
32	1.032	1.0000	1.045
49	1.917	1.306	1.3345
;

proc sql;
select name into: var separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE' and name ne 'id';
quit;

%put &var;

proc sort data=have;
by id;
run;
proc transpose data=have out=trans(rename=(col1=factor)) name=product; 
by id;
var &var.;
run;
Thanks,
Jag
Contributor
Posts: 31

Re: Transpose data

Thank you . What if i have 100's of these files ?

 

is there an easy way

Super User
Posts: 18,997

Re: Transpose data

@jhh197 Please take the time to either 1) Ask a detailed question from the start. 

2) Ask multiple specific questions rather than continuing to build on a question that's been answered and marked as solved.

Solution
‎06-16-2016 09:53 PM
Trusted Advisor
Posts: 1,137

Re: Transpose data

Alternatively by arrays

 

data want;
length product $10.;
set have;
array cm(3) $10 ("company" "store" "price");
array cmsq(3) company store price;
do i = 1 to 3;
product=cm(i);
factor=cmsq(i);
output;
end;
keep id product factor;
run;
Thanks,
Jag
Contributor
Posts: 31

Re: Transpose data

Thank you

Trusted Advisor
Posts: 1,137

Re: Transpose data

you could also automate the array approach as well with the macro variables

 


proc sql;
select quote(strip(name)), name, count(*) into: var separated by ' ', :var2 separated by ' ', :cnt from dictionary.columns where libname='WORK' and memname='HAVE' and name ne 'id';
quit;

%put &var &var2 &cnt;

data want;
length product $10.;
set have;
array cm(&cnt) $10 (&var);
array cmsq(&cnt) &var2;
do i = 1 to &cnt;
product=cm(i);
factor=cmsq(i);
output;
end;
keep id product factor;
run;
Thanks,
Jag
Contributor
Posts: 31

Re: Transpose data

Thanks a lot

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 486 views
  • 3 likes
  • 3 in conversation