Collapsing Medical Claims into Single Row

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Collapsing Medical Claims into Single Row

I am looking for a solution to collapse claims into a single row by claim number and in need of assistance.

Have:

CLAIM_NUMER_ENC_DATECT_SCAN_DATEER_FLAGHEAD_INJ_FLAGCT_SCAN_FLAGCT_SCAN_COST
1..0100
1.01/01/2014001$200
101/01/2014.1000
2..0100
2.01/06/2014001$100
201/04/2014.1000
3..0100
3..0000
301/10/2014.1000
4 .0000
4 .0000
401/10/2014.1000

Want:

CLAIM_NUMER_ENC_DATECT_SCAN_DATEER_FLAGHEAD_INJ_FLAGCT_SCAN_FLAGCT_SCAN_COST
101/01/201401/01/2014111$200
201/04/201401/06/2014111$100
301/10/2014.1100
401/10/2014.1000

Accepted Solutions
Solution
‎12-09-2014 11:01 AM
Super User
Posts: 5,513

Re: Collapsing Medical Claims into Single Row

Posted in reply to mikemangini

If all your variables are numeric, this is easy.  Assuming your data is already sorted by CLAIM_NUM:

proc summary data=have;

  by claim_num;

  var list of numeric variables;

  output out=want (drop=_type_ _freq_) max=;

run;

If some of your variables are character, you will have to leave them out of the VAR statement in PROC SUMMARY.  Instead, bring them in using a second step:

data want;

   update want have (keep=claim_num list of character variables);

   by claim_num;

run;

That should do it.  Good luck.

View solution in original post


All Replies
Super User
Super User
Posts: 7,970

Re: Collapsing Medical Claims into Single Row

Posted in reply to mikemangini

You can do this in several ways e.g. retain - arrays etc.  If there is fixed 3 rows then maybe (not really though too much about the joins here, just typing quickly as leaving):

proc sql;

     create table WANT as

     select     COALESCE(A.CLAIM_NUM,B.CLAIM_NUM,C.CLAIM_NUM) as CLAIM_NUM,

                    A.ER_ENC_DATE,

                    B.CT_SCAN_DATE,

                    A.ER_FLAG,

                    C.HEAD_INJ_FLAG,

                    ...

     from        (select * from HAVE where ER_ENC_DATE is not null) A

     full join    (select * from HAVE where CT_SCAN_DATE is not null) B

     on           A.CLAIM_NUM=B.CLAIM_NUM

     full join    (select * from HAVE where HEAD_INJ_FLAG=1) C

     on           COALESCE(A.CLAIM_NUM,B.CLAIM_NUM)=C.CLAIM_NUM;

quit;

Solution
‎12-09-2014 11:01 AM
Super User
Posts: 5,513

Re: Collapsing Medical Claims into Single Row

Posted in reply to mikemangini

If all your variables are numeric, this is easy.  Assuming your data is already sorted by CLAIM_NUM:

proc summary data=have;

  by claim_num;

  var list of numeric variables;

  output out=want (drop=_type_ _freq_) max=;

run;

If some of your variables are character, you will have to leave them out of the VAR statement in PROC SUMMARY.  Instead, bring them in using a second step:

data want;

   update want have (keep=claim_num list of character variables);

   by claim_num;

run;

That should do it.  Good luck.

Contributor
Posts: 34

Re: Collapsing Medical Claims into Single Row

Posted in reply to Astounding

Thank you for the simple and effective solution!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 179 views
  • 3 likes
  • 3 in conversation