BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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


5 REPLIES 5
DBailey
Lapis Lazuli | Level 10

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
Super User

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
Opal | Level 21

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
Quartz | Level 8

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
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6960 views
  • 0 likes
  • 4 in conversation