Transpose Long to Wide in Enterprise Guide

altijani — Tue, 14 Apr 2020 21:19:09 GMT

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

Re: Transpose Long to Wide in Enterprise Guide

RichardDeVen — Tue, 14 Apr 2020 22:41:41 GMT

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;

Re: Transpose Long to Wide in Enterprise Guide

altijani — Tue, 14 Apr 2020 21:56:24 GMT

Thanks.

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

topic Re: Transpose Long to Wide in Enterprise Guide in SAS Programming

Transpose Long to Wide in Enterprise Guide

Re: Transpose Long to Wide in Enterprise Guide

Re: Transpose Long to Wide in Enterprise Guide