Reshaping multiple columns long to wide

[csanfor2]

I have a dataset that has data as follows:

Name                 Zip       Measure_ID          Score

Hospital A         12345      Rate1                     0.2

Hospital A         12345      Rate2                       .

Hospital A         12345      Rate3                       .

Hospital B          54321     Rate1                     1.5

Hospital B          54321     Rate2                     0.4

Hospital B          54321     Rate3                       . 

Hospital C          24680     Rate1                       .

Hospital C          24680     Rate2                       .

Hospital C          24680     Rate3                       .

I would like to have it by hospital:

Name                 Zip           Rate1     Rate2      Rate3

Hospital A        12345           0.2           .               .

Hospital B        54321           1.5         0.4             .

Hospital C        24680             .             .               .

I sorted the dataset by hospital and started by converting measure_id to columns through an array:

     array measure (3) rate1 rate2 rate3;

But, I can't figure out how to handle the score column since I don't want to give it its own columns, I essentially want to make it the values while measure_id becomes column names. 

[ballardw]

May be easier than you think:

proc transpose data=have out=want (drop=_name_)
   ;
by name;
copy zip;
id measure_id;
run;

Drop=_name_ excludes the column that would have the original variable Score.

[csanfor2 · Accepted solution]

So, that code didn't exactly work for me. I got an error message saying
"NOTE: No variables to transpose."

But it got me thinking! I added a var statement, which worked to give me
the score data as values under the rate columns, but all the information I
needed was in the first row per hospital with multiple duplicate rows per
hospital_name that had blank rate columns, so I deduplicated rows by only
keeping the first occurrence of each hospital. Ultimately, I used this code:


proc transpose data=have out=hosp_wide (drop= _name_ );
by name;
copy zip;
id measure_id;
var score;
run;

data wide_dedup; set hosp_wide;
by name;
if first.name;
run;

Thanks!

##- Please type your reply above this line. Simple formatting, no
attachments. -##

--

E-Mail to and from me, in connection with the transaction
of public business, is subject to the Wyoming Public Records
Act and may be disclosed to third parties.