topic Re: Not Missing in SAS Programming

Not Missing

SASPhile — Wed, 06 Feb 2013 21:59:07 GMT

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

Re: Not Missing

DBailey — Wed, 06 Feb 2013 22:03:33 GMT

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.

Re: Not Missing

Reeza — Wed, 06 Feb 2013 22:04:40 GMT

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;

Re: Not Missing

art297 — Wed, 06 Feb 2013 23:45:15 GMT

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

;

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;

Re: Not Missing

SASPhile — Fri, 08 Feb 2013 18:25:20 GMT

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

Re: Not Missing

SASPhile — Fri, 08 Feb 2013 18:32:19 GMT

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;