I currently have a table that outputs top 3 servicing providers referred to by a given provider in the following fashion:
Provider | Servicer | Percent |
---|---|---|
Provider 1 | Servicer_1_1 | 1st%_1 |
Provider 1 | Servicer_2_1 | 2nd%_1 |
Provider 1 | Servicer_3_1 | 3rd%_1 |
Provider 2 | Servicer_1_2 | 1st%_2 |
Provider 2 | Servicer_2_2 | 2nd%_2 |
Provider 2 | Servicer_3_2 | 3rd%_2 |
. | . | . |
. | . | . |
. | . | . |
Provider K | Servicer_1_K | 1st%_K |
Provider K | Servicer_2_K | 2nd%_K |
Provider K | Servicer_3_K | 3rd%_K |
What I'm hoping for is a table more like the following:
Provider | Top Servicer | Top Percent | Second Servicer | Second Percent | Thrid Servicer | Third Percent |
---|---|---|---|---|---|---|
Provider 1 | Servicer_1_1 | 1st%_1 | Servicer_2_1 | 2nd%_1 | Servicer_3_1 | 3rd%_1 |
Provider 2 | Servicer_1_2 | 1st%_2 | Sercicer_2_2 | 2nd%_2 | Servicer_3_2 | 3rd%_2 |
. | . | . | . | . | . | . |
. | . | . | . | . | . | . |
. | . | . | . | . | . | . |
Provider K | Servicer_1_K | 1st%_K | Servicer_2_K | 2nd%_K | Servicer_3_K | 3rd%_K |
How can I do this?
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
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;
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?
What you're looking for is a Transpose.
Most solutions deal with the fact that there are less than the maximum number.
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
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.