BookmarkSubscribeRSS Feed
acemanhattan
Quartz | Level 8

I currently have a table that outputs top 3 servicing providers referred to by a given provider in the following fashion:

ProviderServicerPercent
Provider 1Servicer_1_11st%_1
Provider 1Servicer_2_12nd%_1
Provider 1Servicer_3_13rd%_1
Provider 2Servicer_1_21st%_2
Provider 2Servicer_2_22nd%_2
Provider 2Servicer_3_23rd%_2
...
...
...
Provider KServicer_1_K1st%_K
Provider KServicer_2_K2nd%_K
Provider KServicer_3_K3rd%_K

What I'm hoping for is a table more like the following:

ProviderTop ServicerTop PercentSecond ServicerSecond PercentThrid ServicerThird Percent
Provider 1Servicer_1_11st%_1Servicer_2_12nd%_1Servicer_3_13rd%_1
Provider 2Servicer_1_21st%_2Sercicer_2_22nd%_2Servicer_3_23rd%_2
.......
.......
.......
Provider KServicer_1_K1st%_KServicer_2_K2nd%_KServicer_3_K3rd%_K

How can I do this?

6 REPLIES 6
Ksharp
Super User

The simplest way is using proc means + idgroup .

Or you could try the MERGE skill me,Matt,Arthur.T have talked about it at :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Code: Program

data have;
input provider $ service $ percent $;
cards;
p1 s1 1
p1 s2 2
p1 s3 3
p2 s1 1
p2 s2 2
;
run;
proc sql;
select max(count) into : n
  from (select count(*) as count from have group by provider );
quit;
proc summary data=have ;
by provider;
output out=want idgroup(out[&n] (service percent)=);
run;

acemanhattan
Quartz | Level 8

I forgot that for some of the providers there aren't 3 providers that have been referred to (e.g. one provider may have only every referred to themselves and one other clinic, or maybe they'd have only "referred" to themselves). Will this make a difference with respect to the code you've posted, or would it have to modified?

Reeza
Super User

What you're looking for is a Transpose. 

Most solutions deal with the fact that there are less than the maximum number. 

Ksharp
Super User

No problem . proc mean + idgroup can handle the variety number of patient.

But you need pick up these 3 largest obs firstly .

Message was edited by: xia keshan

acemanhattan
Quartz | Level 8

Thanks for the replies.

I ended up using the following code:

DATA work.top_3_serv;

  SET work.top_3_allwd_a; By year attributed_tin_nm_rllp;

  * Tells SAS not to reset these variables to

  missing when going to top of datastep;

  FORMAT first second third $CHAR105.;

  RETAIN first second third percent1 percent2 percent3;

  * Set variables to missing when reading new

  member - clear previous member's data!;

  if FIRST.attributed_tin_nm_rllp = 1 then do;

  first=''; second=''; third='';

  percent1=.; percent2=.; percent3=.;

  end;

  if count = 1 then do ;

  first = prov_name_serv; percent1= percent_of_allwd;

  end;

  if count = 2 then do ;

  second = prov_name_serv; percent2= percent_of_allwd;

  end;

  if count = 3 then do ;

  third = prov_name_serv; percent3= percent_of_allwd;

  end;

  * Output variables only when done with member;

  if LAST.attributed_tin_nm_rllp = 1 then OUTPUT;

  KEEP year attributed_tin_nm_rllp first second third percent1 percent2 percent3;

RUN;

data_null__
Jade | Level 19

PROC SUMMARY with IDGROUP can be used to find the top three servicers(name) for any given variable(weight) and output the results in wide format.

proc summary data=sashelp.class nway;
  
class sex;
   output out=top3wt idgroup(max(weight) out[3](weight name)=);
   run;

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
  • 6 replies
  • 1550 views
  • 7 likes
  • 4 in conversation