DATA Step, Macro, Functions and more

Not Missing

Reply
Super Contributor
Posts: 647

Not Missing

  %macro t;

    %do i= 1 %to 3;

    data t&i.;

        set t&i.;

     where key_str is not missing;

  run;

  %end;

  %mend t;

  %t;

Hi,

  The datasets in set statement are already created and they are huge.I want to include only those records where key_str is not missing.Here key_str is numeric in some datasets and character in some. Then export them to csv files.

%macro export;

%do i=1 %to 16;

      proc export data=&&name&i.  outfile="&csv./&&name&i." dbms=dlm replace;
       delimiter='01'x;
       putnames = no;
     run;
%end;
%mend export;

My question is ..instead of re-creating new datasets or re-writing the exisiting datasets,can I use a filter condition in proc export where key_str is not missing?

i could do this proc export data=&&name&i.  , where statement takes care..but that particular dataset may have the key_str as either numeric or charcter.


Super Contributor
Posts: 578

Re: Not Missing

I'm not sure if the "is not null" syntax works in that proc..but..

proc sql;

create view vExport as select * from &&Name&i where key_str is not null;

quit;

should work.

Super User
Posts: 17,912

Re: Not Missing

missing takes character or number so why can't you just add the where to the export?

  proc export data=&&name&i. (where=(not missing(key_var)) outfile="&csv./&&name&i." dbms=dlm replace;
   delimiter='01'x;
   putnames = no;
run;
PROC Star
Posts: 7,364

Re: Not Missing

The conflict in your macros is confusing.  In the first one you select from 1 to 3, then in the second you create 1 thru 16.  Did I misunderstand something?

Regardless, unless you are REALLY comfortable and versed at writing macros, I would just use proc sql to create macro variables that contain the statements you want to issue.

The following doesn't do all that you want, but is just offered as an alternative approach:

data t1;

  input x key_str;

  cards;

1 1

2 .

3 1

;

data t2;

  input x key_str;

  cards;

4 .

5 1

6 .

;

data t3;

  input x key_str;

  cards;

7 1

8 .

9 1

;

data t4;

  input x key_str;

  cards;

0 1

0 1

0 1

;

proc sql noprint;

  select 'd'||strip(substr(memname,2)),

         'if strip(dsn) eq "WORK.'||strip(memname)||'" then output d'||

           strip(substr(memname,2))||';'

    into :filenames separated by " ",

         :ifstmts separated by " "

      from dictionary.tables

        where libname="WORK" and

              substr(memname,1,1)="T" and

              input(substr(memname,2), ? 12.)

                between 1 and 3

  ;

quit;

data &filenames.;

  set t1-t3 (where=(not missing(key_str))) indsname=dsn;

  &ifstmts.

run;

Super Contributor
Posts: 647

Re: Not Missing

The challenge is some datasets have key_str as character and some have numeric.

How to exclude those records where there are many ways the key_str has no vlaues.

like '' ' ' .

Super Contributor
Posts: 647

Re: Not Missing

The following may not take care of all possible missing values:

%macro clean;

       %do i= 1 %to 16;

      data clean.&&name&i.;

         set notclean.&&name&i.;

      where key_str is not missing;

       run;

       %end;

%mend clean;

%clean;

Ask a Question
Discussion stats
  • 5 replies
  • 1100 views
  • 0 likes
  • 4 in conversation