BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mikemangini
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Astounding
PROC Star

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.

mikemangini
Obsidian | Level 7

Thank you for the simple and effective solution!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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