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;
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
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
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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.