Reshaping multiple columns long to wide

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Reshaping multiple columns long to wide

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. 

 

 


Accepted Solutions
Solution
‎07-21-2016 03:02 PM
New Contributor
Posts: 4

Re: Reshaping multiple columns long to wide

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.

View solution in original post


All Replies
Super User
Posts: 11,114

Re: Reshaping multiple columns long to wide

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.

 

Solution
‎07-21-2016 03:02 PM
New Contributor
Posts: 4

Re: Reshaping multiple columns long to wide

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 258 views
  • 2 likes
  • 2 in conversation