Blank datasets created

[adityaa9z]

Below is my code. This basically is a macro which creates dataset based on make and type from sashelp.cars. The output is just fine. But it also creates DS where there are 0 observations. Can someone help me figure out how can I not have those pls.

%macro new;

proc sql noprint;
select distinct make into : a separated by "@" from sashelp.cars;
select distinct type into : b separated by "@" from sashelp.cars;
select count (distinct make) into :n separated by "@" from sashelp.cars;
select count (distinct type) into : m separated by "@" from sashelp.cars;

quit;

%put &a. &n. &b. &m.;

%do i = 1 %to &n;
%do j = 1 %to &m;
%let c = %scan(&a,&i,"@");
%let d = %scan(&b,&j,"@");

proc sql;
create table &c._&d as select * from sashelp.cars where make="&c" and type = "&d";
quit;

%end;
%end;


%mend;
%new;

[art297 · Accepted solution]

One way would be to add another sql step and use it to determine whether to create the dataset. e.g.:

proc sql noprint;
  select make 
    from sashelp.cars
      where make="&c" and type = "&d"
  ;

   %if &sqlobs. gt 0 %then %do;
    create table &c._&d as select * from sashelp.cars where make="&c" and type = "&d";
   %end;
quit;

Art, CEO, AnalystFinder.com

[Astounding]

Insert before the QUIT statement:

%if &sqlobs=0 %then %do;

   drop table &c._&d;

%end;

The issue is that some combinations of values just don't exist in the source data.

[Reeza]

I hope you're doing this to learn macro's and not anything else - 99% of the time splitting datasets like this is not a good idea. See the other ways to do this in the link above.

Besides the solutions above, my recommendation would to make sure you have only all valid type, make combinations and create only those. That would require maybe creating a single variable that could be parsed.  

proc sql;
select distinct catx("@", make, type) into :var_list1-
from sashelp.cars;
quit;

%let n = &sqlobs;

%put &n;