DATA Step, Macro, Functions and more

Blank datasets created

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Blank datasets created

[ Edited ]

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;

 


Accepted Solutions
Solution
‎05-27-2017 03:38 PM
PROC Star
Posts: 7,485

Re: Blank datasets created

[ Edited ]
Posted in reply to adityaa9z

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

View solution in original post


All Replies
Solution
‎05-27-2017 03:38 PM
PROC Star
Posts: 7,485

Re: Blank datasets created

[ Edited ]
Posted in reply to adityaa9z

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

Super User
Posts: 5,515

Re: Blank datasets created

Posted in reply to adityaa9z

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.

Super User
Posts: 19,837

Re: Blank datasets created

Posted in reply to adityaa9z

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 169 views
  • 1 like
  • 4 in conversation