Not Missing

[SASPhile]

  %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.

[DBailey]

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.

[Reeza]

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;

[art297]

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;

[SASPhile]

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 '' ' ' .

[SASPhile]

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;