Search and create column

[Mikkel_madsen]

Hi,

Happy new year!

I got multiple columns named “b_yyww” specifying the year and week, eg. b_0003 and b_9751. The columns are arranged in order b_9601 to b_1352.

I have another column “date of event” specifying the date of at given event (ddmmyy).

I want to search for a specific value “123” in the “b_yyww” columns and create two new columns: “123 before event” and “123 after event” specifying whether 123 occurred before/after the event “yes”/”no”.

Is this possible?

/MM

[PeterClemmensen]

Are these character columns? And is the value to be searched for always "123"?

[Mikkel_madsen]

I am not sure what you mean by character columns, sorry.

The value to be searched for are four different combinations of a 3-digit number (123, 000, 100, and 110).

[PeterClemmensen]

So a simplified version of your data looks like this correct?

data have;
input b_9601-b_9606 date_event :date9.;
format date_event date9.;
datalines;
999 123 888 777 666 123 01Mar1996
999 123 888 777 666 555 01Apr1996
;

[Mikkel_madsen]

Exactly!

[PeterClemmensen · Accepted solution]

See if you can use this as a template. Feel free to ask 🙂

data have;
input b_9601-b_9606 date_event :date9.;
format date_event date9.;
datalines;
999 123 888 777 666 123 01Mar1996
999 123 888 777 666 555 01Apr1996
;

data want;
   set have;
   array b b_:;
   _123before = 'No ';
   _123after = 'No ';
   do over b;
      dt = input(compress(vname(b),, 'kd'), yymmn4.);
      if dt < date_event and b = 123 then _123before = 'Yes';
      if dt > date_event and b = 123 then _123after = 'Yes';
   end;
   drop dt;
run;

Result:

b_9601 ......... b_9606  date_event _123before _123after 
999 123 888 777 666 123  01MAR1996  Yes        Yes 
999 123 888 777 666 555  01APR1996  Yes        No 

[Mikkel_madsen]

b_9601 is yyww (year and week). I tried changing yymmn4 to yywwn4, but get error when I do this.

[Mikkel_madsen]

I used weekv8 instead, and it seems to be working! Thank you!!!

[PeterClemmensen]

Great 🙂 Glad you found your answer.

[PeterClemmensen]

Ok. Try this instead

data have;
input b_9601-b_9606 date_event :date9.;
format date_event date9.;
datalines;
999 123 888 777 666 123 01Mar1996
999 123 888 777 666 555 01Apr1996
;

data want;
   set have;
   array b b_:;
   _123before = 'No ';
   _123after = 'No ';
   do over b;
      dt = input(cats(substr(compress(vname(b),, 'kd'), 1, 2), "W", 
                      substr(compress(vname(b),, 'kd'), 3, 2)), weeku5.);
      if dt < date_event and b = 123 then _123before = 'Yes';
      if dt > date_event and b = 123 then _123after = 'Yes';
   end;
   drop dt;
run;