topic SAS Transpose in SAS Procedures

SAS Transpose

kz134 — Mon, 02 Dec 2019 16:57:09 GMT

Hi,

I have:

Customer	Product_1	Cost_1	Product_2	Cost_2	Product_3	Cost_3
AAA	Book	10	Pen	2	Computer	1000
BBB	Pen	2	Phone	500
CCC	TV	500	Phone	500

I want:

Customer	Product	Cost
AAA	Book	10
AAA	Pen	2
AAA	Computer	1000
BBB	Pen	2
BBB	Phone	500
CCC	TV	500
CCC	Phone	500

Thanks!

Re: SAS Transpose

ed_sas_member — Mon, 02 Dec 2019 17:08:27 GMT

Hi @kz134

You can try this, using an array statement:

data have;
	infile datalines dlm="09"x;
	input Customer $ Product_1 $ Cost_1 Product_2 $ Cost_2 Product_3 $ Cost_3;
	datalines;
AAA	Book	10	Pen	2	Computer	1000
BBB	Pen	2	Phone	500	 	 
CCC	TV	500	Phone	500	 	
;
run;

data want;
	set have;
	
	array product_(3) $;
	array cost_(3) ;
	
	do i=1 to dim(product_);
		if product_(i) ne " " then do;
			product = product_(i);
			cost = cost_(i);
			output;
		end;
	end;
	
	keep customer product cost;
run;

Output:

Re: SAS Transpose

art297 — Mon, 02 Dec 2019 17:55:16 GMT

You could also accomplish the task using the untranspose macro. E.g.:

data have;
	infile datalines dlm="09"x;
	input Customer $ Product_1 $ Cost_1 Product_2 $ Cost_2 Product_3 $ Cost_3;
	datalines;
AAA	Book	10	Pen	2	Computer	1000
BBB	Pen	2	Phone	500	 	 
CCC	TV	500	Phone	500	 	
;
run;

filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=have, out=want (drop=seqnum), copy=Customer, 
  delimiter=_, var=Product Cost, id=seqnum)

You can also download the actual macro and it's documentation at: https://github.com/art297/untranspose

Art, CEO, AnalystFinder.com

Re: SAS Transpose

Reeza — Mon, 02 Dec 2019 18:35:12 GMT

For doing two at a time, an array is the most efficient approach (after @art297 macro).

Transposing data tutorials:
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

@ed_sas_member has provided you the detail code but I'm linking a tutorial that will explain the data and process if you have any issues with understanding how it works.

@kz134 wrote:

Hi,

I have:

Customer Product_1 Cost_1 Product_2 Cost_2 Product_3 Cost_3

AAA Book 10 Pen 2 Computer 1000

BBB Pen 2 Phone 500

CCC TV 500 Phone 500

I want:

Customer Product Cost

AAA Book 10

AAA Pen 2

AAA Computer 1000

BBB Pen 2

BBB Phone 500

CCC TV 500

CCC Phone 500

Thanks!