topic Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row. in SAS Programming

3 largest values per category output in three different rows. How to get 3 largest values on one row.

acemanhattan — Fri, 03 Jul 2015 02:22:04 GMT

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?

Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row.

Ksharp — Fri, 03 Jul 2015 12:54:09 GMT

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;

Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row.

acemanhattan — Fri, 03 Jul 2015 21:01:33 GMT

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?

Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row.

Reeza — Fri, 03 Jul 2015 21:22:28 GMT

What you're looking for is a Transpose.

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

Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row.

Ksharp — Sat, 04 Jul 2015 01:56:06 GMT

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

Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row.

acemanhattan — Fri, 10 Jul 2015 15:13:11 GMT

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;

Re: 3 largest values per category output in three different rows. How to get 3 largest values on one row.

data_null__ — Fri, 10 Jul 2015 17:27:30 GMT

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;