Sorting Horizontal Transposed Columns

[jweds]

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.

[ballardw]

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]

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]

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]

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]

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.