topic Re: Restructure a table in SAS Programming

Restructure a table

di_niu0 — Fri, 06 Aug 2021 23:45:14 GMT

Hi All,

I've got a SAS data set like the one below. The second and third rows are the mean and median for the total fee, respectively. Similarly, the last two rows are the mean and median for the unit, respectively. This table is hard to do analysis. I would like to add some indicators to differentiate the two sets of mean and median. Not sure how to do. Thank you in advance.

Variable Value

total_fee

mean 100

median 110

flag_customer 1000

unit

mean 10

median 12

Re: Restructure a table

Reeza — Sat, 07 Aug 2021 00:09:27 GMT

Use RETAIN, please post your sample data using a data step in the future. Instructions on how to provide data in that format is included here.

Retain to hold values across rows
LENGTH before SET to have variable in the first column rather than the last column
WHERE on output to filter out the missing values after the calculations are done. You could do it in another step or you can add a DELETE statement as well.

data have;
infile cards truncover;
informat variable $14.;
input Variable           Value ;
cards;
total_fee
mean                  100
median                110
flag_customer     1000
unit 
mean                   10
median                12
;;;;
run;

data clean(where=(not missing(value)));
length variable_Summarized $32.;
set have;
retain Variable_Summarized;
if missing(Value) then Variable_Summarized = variable;
drop variable;
run;

proc print;
run;

@di_niu0 wrote:

Hi All,

I've got a SAS data set like the one below. The second and third rows are the mean and median for the total fee, respectively. Similarly, the last two rows are the mean and median for the unit, respectively. This table is hard to do analysis. I would like to add some indicators to differentiate the two sets of mean and median. Not sure how to do. Thank you in advance.

Variable Value

total_fee

mean 100

median 110

flag_customer 1000

unit

mean 10

median 12

Re: Restructure a table

ballardw — Sat, 07 Aug 2021 04:21:45 GMT

@di_niu0 wrote:

Hi All,

I've got a SAS data set like the one below. The second and third rows are the mean and median for the total fee, respectively. Similarly, the last two rows are the mean and median for the unit, respectively. This table is hard to do analysis. I would like to add some indicators to differentiate the two sets of mean and median. Not sure how to do. Thank you in advance.

Variable Value

total_fee

mean 100

median 110

flag_customer 1000

unit

mean 10

median 12

When you say "This table is hard to do analysis." where does it come from? If you create it as the result of one of the SAS procedures such as Proc means then syntax changes when creating it could make a big difference in how the set is made.

I do agree it would be hard to do analysis but I'm not sure what you actually want as indicators might not be the best way. What types of analysis do you think will be done on this data?