topic How to stop a macro if there is a empty data set? in SAS Programming

How to stop a macro if there is a empty data set?

RoWa — Thu, 28 Jun 2018 21:07:31 GMT

Hello I have a macro loop and has two steps, and the second step is transpose, but if the 1st step gets the empty data set (0 observation) then the 2nd step will have error. How can I have a step to test if the data set is empty, if its will jump to next loop. please see example below

%do m=1 %to 12;

%do n=1 %to 6;
/*1ST STEP*/ %Collect-DATA-SET-Step;

/*2ND STEP*/ %TRANSPOSE-Step;
%end;
%end;

What I would like is to have a test step after the 1st step, if the data set is empty, them move to next round loop.

Any thoughts are appreciated. Thank you.

Re: How to stop a macro if there is a empty data set?

Kurt_Bremser — Thu, 28 Jun 2018 21:14:35 GMT

proc sql noprint;
select nobs into :nobs from dictionary.tables
where libname = "XXXXX" and memname = "YYYYY";
quit;

%if &nobs ne 0 %then %do;

/* transpose step */

%end;

Re: How to stop a macro if there is a empty data set?

RoWa — Fri, 29 Jun 2018 16:22:12 GMT

Thank you. I am little confused with "where libname = "xxxxxx" and memname = "YYYYY" ". What am I supposed to fillup for the xxxxxx and YYYYY?

I have posted a sample of my program, and can you please help me to correct it? I have struggled for few hours on this....:(

%do i= 1 %to 2;
%let PRIN1 = %scan(%bquote(&PRINLST), &i, ' ');
%put &prin1;

%do j = 1 %to 2;
%let POS1 = %scan(%bquote(&POS),&j, ' ');
%put &POS1;

%do m=1 %to 2;
%let CLSYRMTH1 = %scan(%bquote(&CLSYRMTH),&m, ' ');
%put &CLSYRMTH1;

%Basetable(&PRIN1, &POS1, &CLSYRMTH1); /*Gather base table*/

proc sql noprint;
select nobs into :nobs
from WORK.T_&PRIN1._&POS1._&CLSYRMTH1
where libname = "WORK" and memname = "YYYYY"
;quit;

%if &nobs ne 0 %then %do;

%TRANSPOSE(&PRIN1, &POS1, &CLSYRMTH1); /*Transpose step*/

%end;

%end;
%end;
%end;

Re: How to stop a macro if there is a empty data set?

Kurt_Bremser — Fri, 29 Jun 2018 16:41:39 GMT

memname is the dataset name (in uppercase) for which you want to get the observation count..

Re: How to stop a macro if there is a empty data set?

RoWa — Fri, 29 Jun 2018 17:23:23 GMT

I figured it out. Thank you.

Re: How to stop a macro if there is a empty data set?

baldcypress — Tue, 04 Jan 2022 04:22:54 GMT

Below is an example using count(*) in PROC SQL:

%macro split(i, file);
%do i=1 %to &i;
proc sql noprint;
select count(*) into :obs_count
from &file._out&i;
quit;
%if &obs_count ne 0 and &file=res %then %do;
proc export data=&file._out&i label /* The label option is used to export the label names as variable names */
outfile="&SF\covid19&file.&i..csv"
dbms=csv replace;
run;
%end;
%else %if &obs_count ne 0 %then %do;
proc export data=&file._out&i
outfile="&SF\covid19&file.&i..csv"
dbms=csv replace;
run;
%end;

%end;
%mend split;