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

## SAS Transpose

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Meteorite | Level 14

## Re: SAS Transpose

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:

3 REPLIES 3
Meteorite | Level 14

## Re: SAS Transpose

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:

Opal | Level 21

## Re: SAS Transpose

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

Super User

## Re: SAS Transpose

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!

Discussion stats
• 3 replies
• 476 views
• 3 likes
• 4 in conversation