How to clear multiple columns with the same conditions using Case Statement?

[annaleticia]

The goal is to clear all values in the 4 columns if the condition was met. Basically same condition applies to all columns.

1. Vehicle in ('Car', Bike') OR

2. DOS>= BEG_DT, then values from columns DOS1, DOS1_ TYPE, DOS2, DOS2_ TYPE, will be cleared out

How to clear multiple columns with the same conditions using Case Statement without the repetition of same condition?

DATA _NULL_; 
CALL SYMPUT('BEG_DT',PUT(INTNX('YEAR',TODAY()-0,0,'BEGIN'),DATE9.));
RUN;

%let ad=('car','bike');

/*Column 1 DOS1 and 2 DOS1_TYPE*/
case when vehicle in &ad. or dos1>="&beg_dt."d then '' else put(dos1,mmddyy10.) end as dos1, 
case when vehicle in &ad. or dos1>="&beg_dt."d then '' else dos1_type end as dos1_type,

/*column 3 dos2 and 4 dos2_type*/
case when vehicle in &ad. or dos1>="&beg_dt."d then '' else put(dos2,mmddyy10.) end as dos2, 
case when vehicle in &ad. or dos1>="&beg_dt."d then '' else dos2_type end as dos2_type

[PaigeMiller]

This could be done in an ARRAY in a SAS data step. Then it doesn't matter how many variables you have, you only have to write the code once.

But, it seems as if you are trying to change DOS1, which is numeric in the original data set, to a character DOS1, and that won't work.

Something like this produces a numeric output DOS1-DOS22. Since these are dates, I feel this is a better way to go.

/* UNTESTED CODE */

data _null_; 
call symput('beg_dt',intnx('year',today(),0,'begin'));
run;

data want;
    set have;
    array dos dos1-dos22;
    do i=1 to 22;
        if vehicle in ('car','bike') and dos(i)>=&beg_dt then dos(i)=.;
    end;
    format dos: mmddyy10.;
    drop i;
run;

PS: From now on, please don't show us partial SQL code, show us the entire code for your PROC SQL

[ballardw]

I don't see anything I would call "clear"ing any column in that snippet.

If you want to apply the same condition to multiple variables then a DATA STEP may be you choice.

If  <your condition(s)> then do;
   <make multiple assignments when true such as>
    var1 = somevalue;
    var2 = othervalue;
end;
else do;
    <what ever is needed when not true>
    var2= whatever;
end;

Since you haven't provided an complete Proc SQL call then there is no evidence that SQL and Case statement(s) are even needed. But SQL is verbose and there isn't any nice short way to combine the same conditions for multiple variables.

[yabwon · Accepted solution]

data have;
infile cards dlm=" ";
input Vehicle : $ 4. DOS1 :date9. DOS1_TYPE DOS2 :date9. DOS2_TYPE;
format DOS1 DOS2 date9.;
cards;
car  23may2022 1 13may2022 4
bike 30may2022 2 23may2022 5
boat 11may2022 3 03may2022 6
;
run;
proc print;
run;

%let ad=('car','bike');
%let beg_dt = 24may2022;

data want;
  set have;
  if vehicle in &ad. or dos1 >= "&beg_dt."d then
    call missing(DOS1, DOS1_TYPE, DOS2, DOS2_TYPE);
run;
proc print;
run;