Hello -
I am working with SAS EG, and my data looks like the data in the file as long. I need to convert that data into wide as in the file.
Thank you, and stay safe
You can perform the reshaping with two TRANSPOSE steps with an intermediate DATA step.
Example:
data have; input ID FirstCount FirstSales SecondCount SecondSales ThirdCount ThirdSales FourthCount FourthSales; informat firstsales secondsales thirdsales fourthsales dollar12.; format firstsales secondsales thirdsales fourthsales dollar12.; datalines; 1 23 $156 25 $65,147 89 $215,654 5 $215 2 38 $107,964 17 $123,032 0 $- 520 $138,101 3 0 $- - $- 151 $514,785 0 $- ; proc transpose data=have out=stage1; by id; run; data stage2; set stage1; length type $27; type = prxchange('s/(count|sales)//i', 1, _name_); var = tranwrd(_name_, trim(type), ''); run; proc transpose data=stage2 out=want(drop=_name_); by id type notsorted; id var; var col1; format sales dollar12.; run;
Similar process to revert to the original shape
* and back again; data have; set want; run; proc transpose data=have out=stage1; by id type notsorted; run; data stage2; set stage1; _newname_ = cats(type,_name_); run; proc transpose data=stage2 out=want(drop=_name_); by id; id _newname_; run;
Note: The OP image shows wide and long not consistent to question.
In general, if you have tall categorical data, a reshaping into the 'wide' shape the task is more a reporting issue than a data transformation issue. Proc TABULATE or REPORT are great for presenting the categorical data in any desired shape.
ods html file='wide.html'; proc tabulate data=tall; class id type / order=data; var count sales; table id='', type='' * (count*sum=''*format=8. sales*sum=''*format=dollar12.) / box='ID'; run; proc report data=tall; column id type,(count sales); define id/group; define type/across order=data; run; ods html close;
You can perform the reshaping with two TRANSPOSE steps with an intermediate DATA step.
Example:
data have; input ID FirstCount FirstSales SecondCount SecondSales ThirdCount ThirdSales FourthCount FourthSales; informat firstsales secondsales thirdsales fourthsales dollar12.; format firstsales secondsales thirdsales fourthsales dollar12.; datalines; 1 23 $156 25 $65,147 89 $215,654 5 $215 2 38 $107,964 17 $123,032 0 $- 520 $138,101 3 0 $- - $- 151 $514,785 0 $- ; proc transpose data=have out=stage1; by id; run; data stage2; set stage1; length type $27; type = prxchange('s/(count|sales)//i', 1, _name_); var = tranwrd(_name_, trim(type), ''); run; proc transpose data=stage2 out=want(drop=_name_); by id type notsorted; id var; var col1; format sales dollar12.; run;
Similar process to revert to the original shape
* and back again; data have; set want; run; proc transpose data=have out=stage1; by id type notsorted; run; data stage2; set stage1; _newname_ = cats(type,_name_); run; proc transpose data=stage2 out=want(drop=_name_); by id; id _newname_; run;
Note: The OP image shows wide and long not consistent to question.
In general, if you have tall categorical data, a reshaping into the 'wide' shape the task is more a reporting issue than a data transformation issue. Proc TABULATE or REPORT are great for presenting the categorical data in any desired shape.
ods html file='wide.html'; proc tabulate data=tall; class id type / order=data; var count sales; table id='', type='' * (count*sum=''*format=8. sales*sum=''*format=dollar12.) / box='ID'; run; proc report data=tall; column id type,(count sales); define id/group; define type/across order=data; run; ods html close;
Thanks.
My data is currently long as in your second code. I need it to be as in your first shape.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: