transpose and make columns have names of pre-transposed values

[Cal_Hottie]

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

[Reeza]

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

---

[PaigeMiller]

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.

[Patrick]

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;