topic Re: transpose and make columns have names of pre-transposed values in SAS Programming

transpose and make columns have names of pre-transposed values

Cal_Hottie — Wed, 13 Nov 2024 20:43:49 GMT

I need to transpose my data with set up as follows:

Original table

zip risk threshold

90001 1.0 18.22

90001 1.5 20.55

90001 2.0 22.01

90001 2.5 25.95

...

96962 1.0 19.24

96962 1.5 21.45

96962 2.0 23.85

96962 2.5 25.47

Desired table:

zip threshold_risk_1.0 threshold_risk_1.5 threshold_risk_2.0 threshold_risk_2.5

90001 18.22 20.55 22.01 25.95

...

96962 19.24 21.45 23.85 25.47

Re: transpose and make columns have names of pre-transposed values

Reeza — Wed, 13 Nov 2024 21:12:01 GMT

PROC TRANSPOSE will get you close, the names aren't quite a match.

proc transpose data=have prefix=threshold_risk_;
by zip;
id risk;
value threshold;
run;

Ensure your data is sorted by ZIP and RISK prior to transpose to have the data in the desired order.

@Cal_Hottie wrote:

I need to transpose my data with set up as follows:



Original table

zip risk threshold

90001 1.0 18.22

90001 1.5 20.55

90001 2.0 22.01

90001 2.5 25.95

...

96962 1.0 19.24

96962 1.5 21.45

96962 2.0 23.85

96962 2.5 25.47

Desired table:

zip threshold_risk_1.0 threshold_risk_1.5 threshold_risk_2.0 threshold_risk_2.5

90001  18.22  20.55  22.01  25.95

...

96962  19.24  21.45  23.85  25.47

Re: transpose and make columns have names of pre-transposed values

PaigeMiller — Wed, 13 Nov 2024 23:15:22 GMT

Transposing a long data set into a wide data set like this is a very poor way to proceed in SAS. This will make your programming a lot more difficult. Please explain what it is you will do once you have this wide data set. What can you do with the wide data set that can't be done with the long data set?

If you want a report with column names indicating the threshold level, don't transpose, use PROC REPORT.

Re: transpose and make columns have names of pre-transposed values

Patrick — Thu, 14 Nov 2024 00:51:58 GMT

Your desired data structure only makes sense if you want to use Proc Print for reporting. It's very sub-optimal for any further data processing starting with variable names that don't comply with SAS naming standards.

Below very close to what @Reeza already proposed with two tweaks:
1. replace Value with correct Var statement in Proc Transpose

2. apply a format to variable risk so the new variables get created with a name as desired.

data have;
  infile datalines truncover;
  input zip risk threshold;
  datalines;
90001 1.0 18.22
90001 1.5 20.55
90001 2.0 22.01
90001 2.5 25.95
96962 1.0 19.24
96962 1.5 21.45
96962 2.0 23.85
96962 2.5 25.47
;

/* Step 1: Ensure data is sorted as required for next step                             */
/*         - skip this step if you can be sure that data is already sorted as required */
proc sort data=have;
  by zip risk;
run;

/* Step 2: Transpose the data */
proc transpose data=have out=transposed(drop=_NAME_) prefix=threshold_risk_;
  format risk f5.1; 
  by zip;
  id risk;
  var threshold;
run;

/* Step 3: Print the transposed data */
proc print data=transposed noobs;
  var zip threshold_risk_:;
run;