BookmarkSubscribeRSS Feed
saskapa
Quartz | Level 8

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

5 REPLIES 5
MikeZdeb
Rhodochrosite | Level 12

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.

MikeZdeb
Rhodochrosite | Level 12

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;

user24feb
Barite | Level 11

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;

saskapa
Quartz | Level 8

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;

Steelers_In_DC
Barite | Level 11

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. 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1646 views
  • 3 likes
  • 4 in conversation