I have the following dataset:
data have;
infile datalines delimiter="|";
input attrib :$30. multiple_attr :$1. id :$30. attrib_id :8. member_value :$100. type :$5. dt_event :datetime18.;
format dt_event datetime20.;
datalines;
TYPE|N|ABC123|111|MEDIUM|Start|01DEC2014:00:00:00
TYPE|N|ABC123|111|MEDIUM|End|18APR2021:00:00:00
TYPE|N|ABC123|111|BIG|Start|19APR2021:00:00:00
TYPE|N|ABC123|111|BIG|End|31DEC2030:00:00:00
POSITION|N|ABC123|222|TOP|Start|01DEC2014:00:00:00
POSITION|N|ABC123|222|TOP|End|31DEC2030:00:00:00
IS_ACTIVE|N|ABC123|333|YES|Start|01DEC2014:00:00:00
IS_ACTIVE|N|ABC123|333|YES|End|31DEC2030:00:00:00
LEVELS|Y|ABC123|1|ALONE|Start|01DEC2014:00:00:00
LEVELS|Y|ABC123|1|BOTH|Start|01DEC2014:00:00:00
LEVELS|Y|ABC123|1|BOTH|End|18APR2021:00:00:00
LEVELS|Y|ABC123|1|ALONE|End|31DEC2030:00:00:00
TYPE|N|DEF456|111|MEDIUM|Start|01DEC2014:00:00:00
TYPE|N|DEF456|111|MEDIUM|End|31DEC2030:00:00:00
POSITION|N|DEF456|222|MID|Start|01DEC2014:00:00:00
POSITION|N|DEF456|222|MID|End|31DEC2030:00:00:00
IS_ACTIVE|N|DEF456|333|YES|Start|01MAR2014:00:00:00
IS_ACTIVE|N|DEF456|333|YES|End|31DEC2030:00:00:00
LEVELS|Y|DEF456|1|ALONE|Start|01MAR2014:00:00:00
LEVELS|Y|DEF456|1|BOTH|Start|01MAR2014:00:00:00
LEVELS|Y|DEF456|1|BOTH|End|31MAR2018:00:00:00
LEVELS|Y|DEF456|1|BOTH|Start|20AUG2018:00:00:00
LEVELS|Y|DEF456|1|ALONE|End|31DEC2030:00:00:00
LEVELS|Y|DEF456|1|BOTH|End|31DEC2030:00:00:00
;
Which is a event based table for all the attributes an ID has.
I would like to be able to "stack" multiple modalities attributes (ex: stacj attrib_id 1 together) so that I end up with the following dataset:
+---------------+--------+-----------+--------------------+--------------------+--------------+ | multiple_attr | id | attrib_id | start_date | end_date | member_value | +---------------+--------+-----------+--------------------+--------------------+--------------+ | Y | ABC123 | 1 | 01DEC2014:00:00:00 | 18APR2021:00:00:00 | ALONE; BOTH | | Y | ABC123 | 1 | 19APR2021:00:00:00 | 31DEC2030:00:00:00 | ALONE | | N | ABC123 | 111 | 01DEC2014:00:00:00 | 18APR2021:00:00:00 | MEDIUM | | N | ABC123 | 111 | 19APR2021:00:00:00 | 31DEC2030:00:00:00 | BIG | | N | ABC123 | 222 | 01DEC2014:00:00:00 | 31DEC2030:00:00:00 | TOP | | N | ABC123 | 333 | 01DEC2014:00:00:00 | 31DEC2030:00:00:00 | YES | | Y | DEF456 | 1 | 01MAR2014:00:00:00 | 31MAR2018:00:00:00 | ALONE; BOTH | | Y | DEF456 | 1 | 01APR2018:00:00:00 | 19AUG2018:00:00:00 | ALONE | | Y | DEF456 | 1 | 20AUG2018:00:00:00 | 31DEC2030:00:00:00 | ALONE; BOTH | | N | DEF456 | 111 | 01DEC2014:00:00:00 | 31DEC2030:00:00:00 | MEDIUM | | N | DEF456 | 222 | 01DEC2014:00:00:00 | 31DEC2030:00:00:00 | MID | | N | DEF456 | 333 | 01MAR2014:00:00:00 | 31DEC2030:00:00:00 | YES | +---------------+--------+-----------+--------------------+--------------------+--------------+
Is there a way to do this?
This sounds like a job for PROC TRANSPOSE, but the logic behind "stacking multiple modalities attributes" is not clear. Can you walk through, in explicit detail, the logic for how attributes are to be "stacked" and which rows should be combined?
Did you thoroughly confirm that your input dataset is complete and correct for deriving your output dataset? If not, please revise as needed.
That being said, a few starter questions about your output dataset:
- Where did the Aug 19, 2018 end_date come from? I don't see that in your input dataset. Was it derived somehow?
- For id ABC123, attrib_id 1, where did the Apr 19, 2021 start_date come from? I don't see that in your input dataset. Was it derived somehow?
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.