topic Re: Check if a variable is missing (through a macro variable) in SAS Procedures

Check if a variable is missing (through a macro variable)

saskapa — Thu, 06 Aug 2015 12:46:10 GMT

Hi,

I have an issue to drop whitin a macro a variable that has a missing value.

Here a sample dataset:

data test;

infile cards missover;

input var1 $ var2 $ var3 $ ;

cards;

ok oki

;

run;

So my var3 is missing ( or is null).

Now I run this macro, to check if the content of var1,var2 or var3 is missing then drop the variable..

%macro test;

data test_;

set test;

%do i=1 %to 3;

if missing(var&i.) then do;

drop var&i.;

end;

%end;

run;

%mend test;

%test;

%macro test2;

data test_;

set test;

%do i=1 %to 3;

if var&i. eq ' ' then do;

drop var&i.;

end;

%end;

run;

%mend test2;

%test2;

%macro test3;

data test_;

set test;

%do i=1 %to 3;

if length(var&i.) eq 1 then do;

drop var&i.;

end;

%end;

run;

%mend test3;

%test3;

Neither of those macro performs the task that should do ( dropping var3), can someone explain me why ?

Regards,

saskap

Re: Check if a variable is missing (through a macro variable)

MikeZdeb — Thu, 06 Aug 2015 13:45:05 GMT

Hi. The DROP statement is NOT an EXECUTABLE statement. It is a DECLARATIVE statement. You cannot use the logic shown in your example to drop a variable from a data set. Take a look at this ... SAS(R) 9.3 Statements: Reference

You'll see that it says ...

DATA step statements are executable or declarative statements that can appear in the DATA step. Executable statements result in some action during individual iterations of the DATA step; declarative statements supply information to SAS and take effect when the system compiles program statements.

Re: Check if a variable is missing (through a macro variable)

MikeZdeb — Thu, 06 Aug 2015 14:02:59 GMT

A suggestion, this paper ... http://www.lexjansen.com/nesug/nesug11/ds/ds12.pdf ... has a routine to drop any variable from a data set that has all missing values.

Here it is (learned the NLEVELS stuff a while back from data _null_) ...

* make a data set with variables to be dropped (all values missing);

data test;

input name : $10. gender : $1.

age height weight;

datalines;

MIKE . 21 . .L 200

MARY . .Z . .H 120

MARK . 45 . .H 110

;

* use PROC FREQ to identify variables with all missing values;

proc format;

value nm low - high = 'OK'

other = .;

value $ch ' ' = ' '

other = 'OK';

run;

ods listing close;

ods output nlevels=tables;

proc freq data=test nlevels;

format _numeric_ nm. _character_ $ch.;

run;

ods output close;

ods listing;

* ensure that data set TABLES always contains the variable NNONMISSLEVELS (needed in next step);

data tables;

retain nnonmisslevels -1;

set tables;

run;

* create a macro variable that lists variables to be dropped;

proc sql noprint;

select tablevar into :droplist

separated by ' '

from tables

where nnonmisslevels eq 0;

quit;

* use a DROP data step option.

data new;

set test (drop=&droplist);

run;

Re: Check if a variable is missing (through a macro variable)

user24feb — Thu, 06 Aug 2015 14:23:37 GMT

The variable itself is not missing. The variable value is missing. You could try something like this:

data test;
infile cards missover;
input var1 $ var2 $ var3 $ ;
cards;
ok oki
;
run;

Proc Transpose Data=test Out=test (Rename=(_NAME_=ID Col1=Value));
Var Var1-Var3;
Run;

Proc Transpose Data=test (Where=(Value is not missing)) Out=test (Drop=_NAME_);
ID ID;
Var Value;
Run;

Re: Check if a variable is missing (through a macro variable)

saskapa — Thu, 06 Aug 2015 14:39:34 GMT

Thanks Mike Zdeb !

Thanks user24feb !

I have found a way to achieve what I want since I can't use my drop statement in the if condition.

%macro test2;

data test_;

set test;

%do i=1 %to 3;

if var&i. eq ' ' then do;

%let drp=var&i.;

end;

%end;

drop &drp.;

run;

%mend test2;

%test2;

Re: Check if a variable is missing (through a macro variable)

Steelers_In_DC — Thu, 06 Aug 2015 14:41:47 GMT

I see you have a link for the solution already. One thing to add:

data _test;

set test;

if missing(var1) then do;drop var1;end;

if missing(var2) then do;drop var2;end;

if missing(var3) then do;drop var3;end;

run;

If you run this you see you get no output, so it's nothing to do with the macro.