topic Re: Concat the data in single cell in SAS Programming

Concat the data in single cell

Sathish_jammy — Thu, 15 Jul 2021 13:43:05 GMT

Hi,

I have to concat the information given in different cell with same ID and Date.

data Have;
input ID Dt mmddyy10. Activity:$30. Act_freq:$30.;
cards;
123	12/19/2010 Walking Daily
123	12/19/2010 NormalWalking Daily
run;

For instance: Have:

ID	Date	Activity	Act_freq
123	19-12-2010	Walking	Daily
123	19-12-2010	NormalWalking	Daily

Want:

ID	Date	Activity	Act_freq
123	19-12-2010	Walking, NormalWalking	Daily, Daily

Kindly suggest a code to get this format.

Thanks in advance!

Re: Concat the data in single cell

novinosrin — Thu, 15 Jul 2021 14:07:24 GMT

data Have;
input ID Dt mmddyy10. Activity:$30. Act_freq:$30.;
format dt mmddyy10.;
cards;
123	12/19/2010 Walking Daily
123	12/19/2010 NormalWalking Daily
run;

data want ;
  do until(last.dt) ;
    set have ;
    by id dt ;
    length _activity _Act_freq  $100 ;
    _activity=catx(', ', _activity, activity) ;
    _Act_freq=catx(', ',_Act_freq, Act_freq) ;
  end ;
  drop activity Act_freq;
  rename _Act_freq=Act_freq _activity=activity ;
run ;

proc print noobs ; run ;

RESULT:

ID	Dt	activity	Act_freq
123	12/19/2010	Walking, NormalWalking	Daily, Daily

Re: Concat the data in single cell

PaigeMiller — Thu, 15 Jul 2021 14:01:27 GMT

I don't really know what you intend to do with this data, but I think anything you could possibly do with this data in SAS would be easier to do if you left the data in the original format.

Re: Concat the data in single cell

ballardw — Thu, 15 Jul 2021 14:22:22 GMT

What is the maximum number of activity or frequency values that could ever be expected to end up in single cell?

You kind of need to know that to allocate a variable long enough to hold that many values plus space for commas and spaces between the values.

Also, just what do you expect to do with that data structure that you can't do with the current one?

(It is sort of amazing how many times I get no answer to this question for basically the same data mangling)

Re: Concat the data in single cell

PaigeMiller — Thu, 15 Jul 2021 14:27:57 GMT

@ballardw wrote:

Also, just what do you expect to do with that data structure that you can't do with the current one?

(It is sort of amazing how many times I get no answer to this question for basically the same data mangling)

I feel the same way.