SAS Transpose

[kz134]

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!

[ed_sas_member · Accepted solution]

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:

[art297]

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

[Reeza]

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!

---