Hello - I am trying to perform a horizontal sort after a proc transpose. I have a field for unique person ID, and then several fields for physicians, with the number of patient visits as the value in each field. The issue is that the fields are actual physician names, not physician1, physician2, etc., so I'm not sure how to sort them in order of patient visits.
Thank you.
Provide some example data and the desired result for that example data. If there are many columns involved probably 4 or 5 will suffice to demonstrate the issue.
If the value for your variables is the number of visits to that physician, you may have to re-think what you are trying to do for a number of reasons. First, the dates are no longer part of your data set (so the supporting information just is not available). Second, the same physician may have been seen for both the first and the last visit (so what would the right order be?) Finally, the order of physicians seen might be different from one patient to the next (so there would not be any order that would be satisfactory across the patient population).
If I correctly understand what you are trying to do, then the following should come at least close to what you are trying to accomplish.
It uses a macro written by Paul Dorfman, which I've attached to this post. I use that macro whenever I need to sort two or more parallel arrays as I'm not familiar with any SAS calls that can accomplish such a task.
Assuming your data looks like the data shown in my example (i.e., fields for each physician's name with the data in those fields representing the number of visits), the following code creates two new sets of arrays/fields docs1-docsN and visit1-visitN) and sorts both according to the number of visits:
data have;
input id john mary sam;
cards;
001 . 2 5
002 3 6 5
003 . 1 .
004 6 5 4
005 4 5 6
;
data want (drop=_: john--sam);
set have;
array _visits(*) john--sam;
array docs(3) $;
array visit(3);
do _i=1 to dim(_visits);
docs(_i)=vname(_visits(_i));
visit(_i)=_visits(_i);
end;
%Qsort (Arr=docs visit, By=visit, Seq=D) ;
run;
Thanks to all for your responses. I decided to simplify the analysis by sorting by beneficiary and number of visits, and then deduping by beneficiary. It was getting too complicated otherwise!
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.