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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RichardDeVen
Barite | Level 11

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;

View solution in original post

2 REPLIES 2
RichardDeVen
Barite | Level 11

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;
altijani
Quartz | Level 8

Thanks.

My data is currently long as in your second code. I need it to be as in your first shape.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 975 views
  • 0 likes
  • 2 in conversation