How to put multiple columns into one efficiently

[JasonL]

Hi there,

I have a dataset like this:

Product    Region01    Region02    Region03  ......  Region14

Cat1              10                 20               30                        45

Cat2             100               200             300                       20

......

And need to transform it into something like this:

Product    Region      Quantity

Cat1         Region01        10

Cat1         Region02        20

Cat1         Region03        30

...........................................

Cat1         Region14        45

Cat2         Region01        100

Cat2         Region02        200

Cat2         Region03        300

..............................................         

Cat2         Region 14        20

.............................................

Is there a way to do this efficiently?  Thanks.

[PaigeMiller]

PROC TRANSPOSE
Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/p03avwj37ggkpkn1mzqzkmwvk1sl.htm

[maguiremq]

data have;
input product $ region01 region02 region03 region04;
infile datalines delimiter = ",";
datalines;
Cat1,10,20,30,45
Cat2,100,200,300,20
;
run;

proc sort data = have;
	by product;
run;

proc transpose data = have out = want (rename = (_name_ = Region col1 = quantity));
	by product;
	var region:;
run;

This is one of many ways to do it. Notice the colon after region -- it is telling SAS to transpose any variable with that prefix.

In the future, please post data in the way that I did it above. It helps us solve the problem quickly.

[JasonL]

In fact, "Region01", etc. are just examples.  I have 14 columns with regional values and they all have random region names, like HNHB, ESC, TC, etc.  How should that be handled?  Thanks.

[PaigeMiller]

Then instead of region: you type in all the variable names.

[JasonL · Accepted solution]

It works.  Thank you both!

[FreelanceReinh]

Hello @JasonL,

Glad to see that the suggested solution worked for you. Then it would be fair and help later readers if you marked the most helpful reply as the accepted solution, not your own "thank you" post. It's very easy to correct that: Select a different post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.