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!
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 |
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 |
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.
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)
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.