BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csanfor2
Fluorite | Level 6

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
csanfor2
Fluorite | Level 6
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

2 REPLIES 2
ballardw
Super User

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
Fluorite | Level 6
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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