topic Re: Reshaping multiple columns long to wide in SAS Programming

Reshaping multiple columns long to wide

csanfor2 — Tue, 05 Jul 2016 19:46:44 GMT

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.

Re: Reshaping multiple columns long to wide

ballardw — Tue, 05 Jul 2016 20:24:53 GMT

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.

Re: Reshaping multiple columns long to wide

csanfor2 — Tue, 05 Jul 2016 21:18:54 GMT

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.