BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adityaa9z
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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
PROC Star

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

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-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
  • 3 replies
  • 970 views
  • 1 like
  • 4 in conversation