group concatinate

Smitha9 — Thu, 14 Jul 2022 15:22:06 GMT

Hi,

I have a dataset

ID SubID Name

1 20 car

1 20 dog

1 20 deer

2 32 fox

2 32 goat

I want to concatenate the Name with comma.

ID SubID Name

1 20 car, dog, deer

2 32 fox, goat

thank you.

Re: group concatinate

Reeza — Thu, 14 Jul 2022 15:37:59 GMT

*create sample data for demonstration;
data have;
    infile cards dlm='09'x;
    input OrgID Product $   States $;
    cards;
1   football    DC
1   football    VA
1   football    MD
2   football    CA
3   football    NV
3   football    CA
;
run;

*Sort - required for both options;
proc sort data=have;
    by orgID;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else
        combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
    by orgID;
    var states;
run;

data want_option2;
    set wide;
    length combined $100.;
    combined=catx(', ', of state_:);
run;

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

@Smitha9 wrote:

Hi,

I have a dataset

ID   SubID Name

1       20       car

1       20       dog

1       20       deer

2      32       fox

2      32        goat

I want to concatenate the Name with comma.

ID         SubID        Name

1           20             car, dog, deer

2           32             fox, goat

thank you.

topic group concatinate in New SAS User

group concatinate

Re: group concatinate