topic Re: Roll Up Data in SAS Programming

Roll Up Data

PBsas — Thu, 01 Jun 2017 02:21:13 GMT

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.

Re: Roll Up Data

kiranv_ — Thu, 01 Jun 2017 02:49:50 GMT

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-p/132882

Re: Roll Up Data

Shmuel — Thu, 01 Jun 2017 04:55:35 GMT

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;

Re: Roll Up Data

PBsas — Thu, 01 Jun 2017 14:02:59 GMT

Thank you for your resposes. 🙂