Stack multiple modalities attributes based on multiple events

legends1337 — Tue, 03 May 2022 16:23:01 GMT

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?

Re: Stack multiple modalities attributes based on multiple events

mklangley — Wed, 04 May 2022 14:43:06 GMT

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?

topic Stack multiple modalities attributes based on multiple events in SAS Programming

Stack multiple modalities attributes based on multiple events

Re: Stack multiple modalities attributes based on multiple events