BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jhh197
Pyrite | Level 9

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

9 REPLIES 9
Jagadishkatam
Amethyst | Level 16

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
jhh197
Pyrite | Level 9

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
Amethyst | Level 16

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
jhh197
Pyrite | Level 9

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

 

is there an easy way

Reeza
Super User

@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
Amethyst | Level 16

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
jhh197
Pyrite | Level 9

Thank you

Jagadishkatam
Amethyst | Level 16

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
jhh197
Pyrite | Level 9

Thanks a lot

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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