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 |
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;
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;
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
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;
Thank you . What if i have 100's of these files ?
is there an easy way
@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.
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;
Thank you
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 a lot
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.