Transpose data

[jhh197]

Hi ,

I have a dataset like below

id	company	store	price
99	1.0000	1.0000	1.0000
32	1.032	1.0000	1.045
49	1.917	1.306	1.3345

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

credit	product	Factor
99	company	1.0000
32	company	1.032
49	company	1.917
99	store	1.0000
32	store	1.0000
49	store	1.306
99	price	1.0000
32	price	1.045
49	price	1.3345

[Jagadishkatam]

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;

[jhh197]

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

[Jagadishkatam]

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;

[jhh197]

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

is there an easy way

[Reeza]

@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.

[Jagadishkatam · Accepted solution]

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;

[jhh197]

Thank you

[Jagadishkatam]

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;

[jhh197]

Thanks a lot