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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.