topic Re: How to create a comma separated String from values from a variable in SAS Programming

How to create a comma separated String from values from a variable

Apprentice — Tue, 12 May 2020 02:13:36 GMT

I have a Table with variable A and B with 5 obs. I am trying to create a string with values concatenated from the variable B into one string grouped by variable A

Example:

A1 = 123; B1 = "XXX";

A2 = 123; B2 = 'YYY';

A3 = 456; B3 = 'ZZZ';

A4 = 456; B4 = 'PPP';

A5 = 456; B5 = 'OOO'

Driven by 2 distinct values stored in variable A; two observations of variable Joined_Variable should be created from variable B.

Joined_Variable = 'XXX, YYY'

Joined_Variable = 'ZZZ, PPP, OOO'

Thank you for your kind help.

Re: How to create a comma separated String from values from a variable

Reeza — Tue, 12 May 2020 02:39:06 GMT

There are two methods illustrated here:
https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

Re: How to create a comma separated String from values from a variable

RichardDeVen — Tue, 12 May 2020 13:19:32 GMT

Accumulate a concatenation over the group and implicit output of one row per group

data have;
input A B $; datalines;
123 XXX
123 YYY
456 ZZZ
456 PPP
456 OOO
run;

data want(keep=A list);
  do until (last.A);
    set have;
    by A;
    length list $30;     * declarative statement (in PDV column will be after(right of) columns of data set), value is implicitly reset at top of step;
    list = catx(', ',list,B);
  end;
run;

Re: How to create a comma separated String from values from a variable

ChrisNZ — Tue, 12 May 2020 02:55:14 GMT

Another example of the gaping need for a few functions as requested here and here.

Re: How to create a comma separated String from values from a variable

Apprentice — Tue, 12 May 2020 06:44:51 GMT

Thanks Richard. Worked like a charm.

Re: How to create a comma separated String from values from a variable

Apprentice — Tue, 12 May 2020 06:45:56 GMT

Thank you Rezza. Great solutions. Both would work like a charm. Much appreciate your help.

Re: How to create a comma separated String from values from a variable

ChrisNZ — Tue, 12 May 2020 08:45:06 GMT

@RichardDeVen Why not simply

data want(keep=A list);
  do until (last.A);
    set have;
    by A;
    length list $30;    
    list = catx(', ',list,B);
  end;
run;

Re: How to create a comma separated String from values from a variable

RichardDeVen — Tue, 12 May 2020 13:18:38 GMT

Extraneous SET was an artifact for earlier typing... edited out.