topic Re: Blank datasets created in SAS Programming

Blank datasets created

adityaa9z — Thu, 25 May 2017 20:05:57 GMT

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;

Re: Blank datasets created

art297 — Thu, 25 May 2017 20:20:47 GMT

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

Re: Blank datasets created

Astounding — Thu, 25 May 2017 20:22:35 GMT

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.

Re: Blank datasets created

Reeza — Fri, 26 May 2017 00:18:26 GMT

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;