DATA Step, Macro, Functions and more

Roll Up Data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Roll Up Data

Hello Experts - I have the following have dataset and I need the want data set.

 

Data have;

input claim_id $ visit;

datalines;

A0001 1

A0001 2

A0001 3

B0001 5

B0001 3

;

run;

 

what I need is:

 

Want:

Claim_id   Visit

A0001       1,2,3

B0001       5,3

 

 Any help is appreciated.


Accepted Solutions
Solution
‎06-01-2017 10:02 AM
Trusted Advisor
Posts: 1,375

Re: Roll Up Data

Alternative solution:

 

Proc sort data=have; by claim_id visit; run; /* assuming data is not sorted already */

data want;
 set have(rename=(visit=visit1));
  by claim_id;
       length visit $10;  /* adapt length to max number of visits */
       retain visit;
       if first.claim_id then visit=' ';
       visit = catx(',' , visit, visit1);
       if last.claim_id then output;
run;

View solution in original post


All Replies
PROC Star
Posts: 252

Re: Roll Up Data

data want;
length claim_id $5. cat $20.;
do until (last.claim_id);
set have;
by claim_id notsorted;
cat=catx(',',cat,visit);
end;
drop visit;
run;
proc print;run;

 

My answer as is copy of @Haikuo previous answer shown below.

https://communities.sas.com/t5/Base-SAS-Programming/Concatenate-multiple-rows-into-a-single-value/td...

Solution
‎06-01-2017 10:02 AM
Trusted Advisor
Posts: 1,375

Re: Roll Up Data

Alternative solution:

 

Proc sort data=have; by claim_id visit; run; /* assuming data is not sorted already */

data want;
 set have(rename=(visit=visit1));
  by claim_id;
       length visit $10;  /* adapt length to max number of visits */
       retain visit;
       if first.claim_id then visit=' ';
       visit = catx(',' , visit, visit1);
       if last.claim_id then output;
run;
Occasional Contributor
Posts: 19

Re: Roll Up Data

Thank you for your resposes. Smiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 142 views
  • 2 likes
  • 3 in conversation