topic Re: With proc transpose, how do you rename columns with specific values from a variable? in SAS Programming

With proc transpose, how do you rename columns with specific values from a variable?

ANKH1 — Wed, 01 Nov 2023 21:18:37 GMT

I have this output:

mean	min_max	median	group
32	1-40	35	Chef/owner
33	2-45	31	Chef's assistant
31	4-39	28	Waiter

With proc transpose I get this:

_NAME_	COL1	COL2	COL3
mean	32	33	31
min_max	1-40	2-45	4-39
median	35	31	28

I would like rename the COL1, COL2, COL3 in the same procedure so it can look like this:

_NAME_	Chef/owner	Chef's assistant	Waiter
mean	32	33	31
min_max	1-40	2-45	4-39
median	35	31	28

I need the output to look like this since this will be used in a macro. I want to avoid renaming each col with a specific name since this macro will also be used for other grouping variable (other that chef/owner, chef´s assistant and waiter).

Thanks!

Re: With proc transpose, how do you rename columns with specific values from a variable?

Tom — Wed, 01 Nov 2023 21:38:31 GMT

Use the ID statement.

data have;
  input mean  min_max $  median  group $20.;
cards;
32  1-40  35  Chef/owner
33  2-45  31  Chef's assistant
31  4-39  28  Waiter
;

proc transpose data=have out=want;
  id group;
  var mean min_max median;
run;

proc print;
run;

Of course that will make them all as CHARACTER variables to be able to hold strings like '2-45'. So values like 32 will get converted to

'          32'

And SAS will have to convert those strange group names into actual variable names.

                                    Chef_s_
Obs    _NAME_      Chef_owner      assistant         Waiter

 1     mean                 32              33              31
 2     min_max    1-40            2-45            4-39
 3     median               35              31              28

Re: With proc transpose, how do you rename columns with specific values from a variable?

ballardw — Wed, 01 Nov 2023 22:26:33 GMT

Personally I find any variable that has two values, i.e. Min and MAX in a single variable basically a failure waiting to happen.

Are you sure that you need a data set? It might be easier, especially if your "min_max" was two separate numeric variable to create a report with appearance as desired.

Unless you read data in that strange format, might even be easier than starting with this summary.

Re: With proc transpose, how do you rename columns with specific values from a variable?

PaigeMiller — Thu, 02 Nov 2023 11:09:03 GMT

@ANKH1

Why would you do such a transpose in the first place? If you are doing this to create a report, don't transpose, use PROC REPORT. You will have many more options for controlling the column names. Please let us know if you are trying to create a report.

Re: With proc transpose, how do you rename columns with specific values from a variable?

ANKH1 — Fri, 03 Nov 2023 12:46:44 GMT

Thank you!

Re: With proc transpose, how do you rename columns with specific values from a variable?

ANKH1 — Fri, 03 Nov 2023 12:49:48 GMT

Thank you for your insight. The min-max is a character value.

Re: With proc transpose, how do you rename columns with specific values from a variable?

ANKH1 — Fri, 03 Nov 2023 12:51:30 GMT

Thank you for your insight. This is only a step before creating a table using proc report.

Re: With proc transpose, how do you rename columns with specific values from a variable?

PaigeMiller — Fri, 03 Nov 2023 13:15:14 GMT

@ANKH1 wrote:
Thank you for your insight. This is only a step before creating a table using proc report.

Then, @ANKH1 you do NOT want to do a transpose before PROC REPORT.