Hello,
I have the below dataset where some events happen. C- stands for Create and D- stands for Delete.
At some point in time, I am creating some values (i.e. C-a) or delete them (D-a) within an array of N Events.
My task would be to retain the previous created values along with the new created values, and if a detelete event occurs, then the output should stop retaining the value.
data have;
infile datalines delimiter=',';
input ID date mmddyy10. Event1 $ Event2 $ Event3 $ Event4 $;
format date date9.;
datalines;
1,03-31-2013,C-a,C-b,.,.
1,05-31-2013,D-b,.,.,.
1,07-31-2013,C-d,C-e,C-f,.
1,08-31-2013,D-e,C-g,.,.
2,04-30-2018,C-h,C-i,D-h,C-j
2,07-31-2018,C-k,.,.,.
2,12-31-2018,D-i,C-o,D-j,D-k
;
run;
data want;
infile datalines delimiter=',';
input ID date mmddyy10. Event1 $ Event2 $ Event3 $ Event4 $;
format date date9.;
datalines;
1,03-31-2013,C-a,C-b,.,.
1,05-31-2013,C-a,.,.,.
1,07-31-2013,C-a,C-d,C-e,C-f
1,08-31-2013,C-a,C-d,C-f,C-g
2,04-30-2018,C-i,C-j,.,.
2,07-31-2018,C-i,C-j,C-k,.
2,12-31-2018,C-o,.,.,.
;
run;
I would very much appreciate your help.
Thank you!
hey, try this:
data want; length list $16; set have; by id; retain list ''; array events(*) Event1-Event4; if first.id then call missing(list); *Determine list of events; do y=1 to dim(events); if not missing(events(y)) then do; if not prxmatch('/(C|D)-[a-z]/',events(y)) then put 'W' 'ARNING: event does not respect expected syntax' events(y)=; list=left(compbl(ifc(substr(events(y),1,1) eq 'C',catx(' ',list,events(y)),tranwrd(list,'C-'||substr(events(y),3,1),'')))); end; end; *reassign events according to their position in the list; do z=1 to dim(events); events(z)=scan(list,z,' '); end; drop list y z; run;
- Cheers -
hey, try this:
data want; length list $16; set have; by id; retain list ''; array events(*) Event1-Event4; if first.id then call missing(list); *Determine list of events; do y=1 to dim(events); if not missing(events(y)) then do; if not prxmatch('/(C|D)-[a-z]/',events(y)) then put 'W' 'ARNING: event does not respect expected syntax' events(y)=; list=left(compbl(ifc(substr(events(y),1,1) eq 'C',catx(' ',list,events(y)),tranwrd(list,'C-'||substr(events(y),3,1),'')))); end; end; *reassign events according to their position in the list; do z=1 to dim(events); events(z)=scan(list,z,' '); end; drop list y z; run;
- Cheers -
Thank you very much!
You can not maintain the current Event values in the same variables that the Event values are being read in from the SET statement. When the data flow reaches the SET, any retained values will be over written. So, instead, maintain state in a separate array.
Searching for the slot containing a value to delete or a slot containing a blank to place a value is done with iteration in a simple loop. Deletion is performed by shifting, leftward by 1 position, all values to the right. Creation is limited to array size of state so that no index errors occur. The code does not log messages what might be improper operations -- i.e. no space to create a value, or a delete value was not found.
Example:
data want; set have; by id; array event event1-event4; length state1-state4 $4; array state state1-state4; retain state:; if first.id then call missing(of state:); do index=1 to dim(event); operation = substr(event(index),1,1); value = substr(event(index),3); if operation = 'D' then do searchIndex = 1 to dim(state); if state(searchIndex) = value then do moveIndex = searchIndex to dim(state)-1; state(moveIndex) = state(moveIndex+1); state(moveIndex+1) = ' '; end; end; else if operation = 'C' then do searchIndex = 1 to dim(state); if not missing(state(searchIndex)) then continue; state(searchIndex) = value; leave; end; end; keep id event: state:; run;
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.