check all entries in a column sas and return a dummy variable sas

Question

I am new in SAS and want to check if all entries in a variable in a data set satisfy a condition (namely =1) and return just one dummy variable 0 pr one depending whether all entries in the variable are 1 or at least one is not 1. Any idea how to do it?

IF colvar = 1 THEN dummy_variable = 1

creates another variable dummy_variable of the same size as the original variable.

Thank you

What are you planning to do with this dummy variable? Do you want to add as new variable and replicate its value for every observation? Do you want make a new dataset that has just this one variable and one observation? Or perhaps you just need to generate a macro variable that you can test later with either macro logic or in another data step. — Tom, Dec 17 '16 at 01:03

score 0 · Answer 1 · answered Dec 16 '16 at 19:33

* Generate test data;
data have;
    colvar=0;
    colvar2=0;
    do i=1 to 20;
        colvar=round(ranuni(0));
        output;
    end;
    drop i;
run;

* Read the input dataset twice, first counting the number
* of observations and setting the dummy variables to 1 if
* the corresponding variable has the value 1 in any obser-
* vation, second outputting the result. The dummy variables
* remain unchanged during the second loop.;
data want;
    _n_=0;
    d_colvar=0;
    d_colvar2=0;
    do until (eof);
        set have end=eof;
        if colvar = 1
         then d_colvar=1;
        if colvar2 = 1
         then d_colvar2=1;
        * etc.... *;
        _n_=_n_+1;
    end;
    do _n_=1 to _n_;
        set have;
        output;
    end;
run;

score 0 · Answer 2 · answered Dec 17 '16 at 01:13

PROC SQL is a good tool for quickly generating a summary of an arbitrarily defined condition. What exactly your condition is is not clear. I think you want the ALL_ONE value in the table the code below generates. That will be 1 when every observation has COLVAR=1. Any value that is NOT a one will cause the condition to be false (0) and so ALL_ONE will then have a value of 0 instead of 1.

You could store the result into a small table.

proc sql ;
  create table check_if_one as
     select min( colvar=1 ) as all_one
          , max( colvar=1 ) as any_one
          , max( colvar ne 1 ) as any_not_one
          , min( colvar ne 1 ) as all_not_one
     from my_table
  ;
quit;

But you could also just store the value into a macro variable that you could easily use later for some purpose.

proc sql noprint ;
   select min( colvar=1 ) into :all_one trimmed from my_table ;
quit;

check all entries in a column sas and return a dummy variable sas

2 Answers2