BookmarkSubscribeRSS Feed
jweds
Fluorite | Level 6

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.

5 REPLIES 5
ballardw
Super User

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.

 

 

Astounding
PROC Star

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).

art297
Opal | Level 21

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;

 

jweds
Fluorite | Level 6

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!

HB
Barite | Level 11 HB
Barite | Level 11
Don't ignore that you may have a database design issue. If every time you get a new physician you have to add a column to each record, you have a problem. Better would be a relational structure in which you have a table of physicians with a code for each. Then a table of patients with a code for each. Lastly, a table of interactions in which each record is an interaction containing a physician code, a patient code, a date, and whatever else might be needed. Makes counting interactions by patient, interactions by physician, or interactions by date or type much easier.

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
  • 5 replies
  • 728 views
  • 4 likes
  • 5 in conversation