topic Re: How to concatenate multiple rows into one row with concatenated column in SAS Enterprise Guide

How to concatenate multiple rows into one row with concatenated column

accesnh — Wed, 06 Mar 2019 14:59:05 GMT

I'm normally a point and click EG user, so I'm having trouble writing the code to produce the following results by writing code.

One issue I have is each ID_NUM can have an unknown count of CSC's associated to it.

work.DO
Office	ID_NUM	Date of Birth	BOSS_ID	CSC
06	0233673	9/15/2010	60118	175X
06	0233673	9/15/2010	60118	152X
06	0233673	9/16/2010	60119	280X
06	0549153	5/18/1999	60321	921X
06	0549153	5/18/1999	60321	929X

to...

work.DO_NEW
Office	ID_NUM	Date of Birth	BOSS_ID	CSC_NEW
06	0233673	9/15/2010	60118	175X, 152X, 280X
06	0549153	5/18/1999	60321	921X, 929X

Thanks for any help provided!

Re: How to concatenate multiple rows into one row with concatenated column

RW9 — Wed, 06 Mar 2019 15:14:03 GMT

There are a lot of posts on this topic on here already, please check search bar before posting. Retain and by:

data want (drop=csc);
  set have;
  by office id_num;
  length csc_new $200;
  retain csc_new;
  csc_new=ifc(first.id_num,csc,catx(',',csc_new,csc));
  if last.id_num then output;
run;

Do note, assumes sorted. Also your test data has 2 boss_id's in one id_num, could be an issue?

Re: How to concatenate multiple rows into one row with concatenated column

ballardw — Wed, 06 Mar 2019 16:49:44 GMT

How is the variable CSC_NEW going to be used?

With the value of CSC ever be duplicated within any ID_NUM group? If so what would the final result look like?

What is the explicit rule that BOSS_id 60119 is grouped with 60118? Is this grouping strictly on ID_num and the date of birth and boss_id are taken from the first row of ID_NUM?

Re: How to concatenate multiple rows into one row with concatenated column

accesnh — Wed, 06 Mar 2019 16:53:02 GMT

the 60119 and 60118 was a typo. The above mentioned code worked perfectly. No, the CSC will never be a duplicate because I'm making that a distinct field in a prior query. Thanks!

Re: How to concatenate multiple rows into one row with concatenated column

RW9 — Thu, 07 Mar 2019 08:52:24 GMT

Do note, @ballardw makes a very good point. If that concatenated variable is not being used for just an output file (i.e. just printed), then it is far better to keep data items in their own individual variables for processing. It is not good practice to program with variables containing multiple data items.