topic Re: Transpose data in SAS Programming

Transpose data

jhh197 — Fri, 17 Jun 2016 00:26:59 GMT

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

Re: Transpose data

Jagadishkatam — Fri, 17 Jun 2016 01:03:23 GMT

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;

Re: Transpose data

jhh197 — Fri, 17 Jun 2016 01:36:34 GMT

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

Re: Transpose data

Jagadishkatam — Fri, 17 Jun 2016 01:40:14 GMT

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;

Re: Transpose data

Jagadishkatam — Fri, 17 Jun 2016 01:50:39 GMT

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;

Re: Transpose data

jhh197 — Fri, 17 Jun 2016 01:53:33 GMT

Thank you

Re: Transpose data

Jagadishkatam — Fri, 17 Jun 2016 01:56:32 GMT

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;

Re: Transpose data

jhh197 — Fri, 17 Jun 2016 02:10:47 GMT

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

is there an easy way

Re: Transpose data

jhh197 — Fri, 17 Jun 2016 02:31:21 GMT

Thanks a lot

Re: Transpose data

Reeza — Fri, 17 Jun 2016 02:31:54 GMT

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