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

The following code is provided by data_null__ for my prior question. the code works perfect when there do exist empty datasets.

 

Then I tried to apply it to rolling window regression. some rolling windows' regressions generate empty tables, while other rolling windows' regressions do not generate empty tables.  The SAS program stop in the rolling window where there is no empty table.

 

Anyway to force the program to continue processing when there is no empty table? Thanks a lot!

 

 

proc sql noprint; 
   select memname into :zero separated by ' '
      from dictionary.tables 
      where libname eq 'WORK' and memname eqt 'HAVE_' and nobs eq 0; 
   quit; 
   run; 
%put NOTE: &=zero;
data &zero;
   do _n_ = 1 to 3; 
      output; 
      end; 
   stop; 
   set &zero;
   run; 

 

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

I believe all you need to do is assign ZERO=_NULL_ before calling PROC SQL.  PROC SQL does not create and INTO variable when "No rows were selected";  Assignin _NULL_ will allow the data step to execute without error but do nothing.

24         %let zero=_NULL_;
25         proc sql noprint;
26            select memname into :zero separated by ' '
27               from dictionary.tables
28               where libname eq 'WORK' and memname eqt 'HAVE_' and nobs eq 0;
NOTE: No rows were selected.
29            quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds
      

30            run;
31         %put NOTE: &=zero;
NOTE: ZERO=_NULL_
32         data &zero;
33            do _n_ = 1 to 3;
34               output;
35               end;
36            stop;
37            set &zero;
38            run;

NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Do you mean the file does not exist at all?   If so then use the exist() function:

data _null_;

  if %sysfunc(exist(<libname>.<dataset>))=0 then call execute('proc sql; create table <libname>.<dataset> (a num); quit;');

...

run;

 

The above will check if <libname>.<dataset> exists and if not create it with a variable a and no records.  Then it will exist for the =0 calculation.

 

To be honest though, if your previous code is not generating what you want, then I would look at the process you are doing, and see if you can create logic which does work as intended - see my reply to your other post - there is no need to split things out or create lists, it can all be done in base SAS with varying structures.  Otherwise you end up creating other problems like this.

 

Jonate_H
Quartz | Level 8

Thank you for your advice. since the original program is wrote in that way, it will take a lot of effort to rewrit it. so I thought it would save my time to just "fixed" it. I agree with you that if I write a code from sketch I should start with a "simple" way from every beginning.

data_null__
Jade | Level 19

I believe all you need to do is assign ZERO=_NULL_ before calling PROC SQL.  PROC SQL does not create and INTO variable when "No rows were selected";  Assignin _NULL_ will allow the data step to execute without error but do nothing.

24         %let zero=_NULL_;
25         proc sql noprint;
26            select memname into :zero separated by ' '
27               from dictionary.tables
28               where libname eq 'WORK' and memname eqt 'HAVE_' and nobs eq 0;
NOTE: No rows were selected.
29            quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds
      

30            run;
31         %put NOTE: &=zero;
NOTE: ZERO=_NULL_
32         data &zero;
33            do _n_ = 1 to 3;
34               output;
35               end;
36            stop;
37            set &zero;
38            run;

NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

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
  • 4 replies
  • 632 views
  • 2 likes
  • 3 in conversation