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

Hi,

 

I have:

 

Customer Product_1Cost_1Product_2Cost_2Product_3Cost_3
AAABook10Pen2Computer1000
BBBPen2Phone500  
CCCTV500Phone500 

 

 

I want:

CustomerProductCost
AAABook10
AAAPen2
AAAComputer1000
BBBPen2
BBBPhone500
CCCTV500
CCCPhone 500

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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:

Capture d’écran 2019-12-02 à 18.07.08.png

View solution in original post

3 REPLIES 3
ed_sas_member
Meteorite | Level 14

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:

Capture d’écran 2019-12-02 à 18.07.08.png

art297
Opal | Level 21

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
Super User

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!


 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 478 views
  • 3 likes
  • 4 in conversation