topic Re: How to put multiple columns into one efficiently in SAS Programming

How to put multiple columns into one efficiently

JasonL — Fri, 02 Jul 2021 16:10:33 GMT

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.

Re: How to put multiple columns into one efficiently

PaigeMiller — Fri, 02 Jul 2021 16:13:38 GMT

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

Re: How to put multiple columns into one efficiently

maguiremq — Fri, 02 Jul 2021 16:19:47 GMT

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.

Re: How to put multiple columns into one efficiently

JasonL — Fri, 02 Jul 2021 16:20:55 GMT

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.

Re: How to put multiple columns into one efficiently

PaigeMiller — Fri, 02 Jul 2021 16:22:04 GMT

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

Re: How to put multiple columns into one efficiently

JasonL — Fri, 02 Jul 2021 16:33:05 GMT

It works. Thank you both!

Re: How to put multiple columns into one efficiently

FreelanceReinh — Fri, 02 Jul 2021 19:29:40 GMT

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.