topic Macro issue when dataset has 0 observations in SAS Programming

Macro issue when dataset has 0 observations

kmardinian — Thu, 11 Oct 2018 22:17:35 GMT

Hi, I created this macro below and have been running it with datasets that contain observations, but as soon as one dataset has 0 observations that fit the criteria, it seems that the macro spits out numbers from the dataset that was run previously. Here is my code below

%macro num (vorder, var);

data final_&vorder;
set t5;
if &var ne .;
run;

proc summary data=final_&vorder completetypes chartype missing noprint;
class outcome trt;
types outcome*trt;
output out=discrete;
run;

proc freq data=discrete;
by _type_;
tables outcome*trt/norow nopercent;
weight _freq_/zeros;
ods output crosstabfreqs_&vorder=crosstabfreqs_&vorder (where=(_type_2='01' or _type_2='11'));
run;

data CrossTabFreqs_&vorder /*(keep=vname rowlabel cell _level_ idlabel _type_)*/;
length vname $32 rowlabel $100 cell $32 idlabel $256;
set CrossTabFreqs;
vname =vnamex(scan(table,2,' '));
rowlabel=vvalueX(vname);
cell=cats(Frequency);
format colPercent 8.1;
set bign key=trt_dec/unique;
idlabel=cat(strip(vvalue(trt_dec)),' ^ ',cat('(N = ',bign,')'));
run;

Is there a way to create another criteria that tells SAS to return nothing if there are no observations in the dataset? Thank you, any help is much appreciated!

Re: Macro issue when dataset has 0 observations

ballardw — Thu, 11 Oct 2018 22:29:43 GMT

@kmardinian wrote:

Hi, I created this macro below and have been running it with datasets that contain observations, but as soon as one dataset has 0 observations that fit the criteria, it seems that the macro spits out numbers from the dataset that was run previously. Here is my code below

%macro num (vorder, var);

data final_&vorder;
set t5;
if &var ne .;
run;

proc summary data=final_&vorder completetypes chartype missing noprint;
class outcome trt;
types outcome*trt;
output out=discrete;
run;

proc freq data=discrete;
by _type_;
tables outcome*trt/norow nopercent;
weight _freq_/zeros;
ods output crosstabfreqs_&vorder=crosstabfreqs_&vorder (where=(_type_2='01' or _type_2='11'));
run;

data CrossTabFreqs_&vorder /*(keep=vname rowlabel cell _level_ idlabel _type_)*/;
length vname $32 rowlabel $100 cell $32 idlabel $256;
set CrossTabFreqs;
vname =vnamex(scan(table,2,' '));
rowlabel=vvalueX(vname);
cell=cats(Frequency);
format colPercent 8.1;
set bign key=trt_dec/unique;
idlabel=cat(strip(vvalue(trt_dec)),' ^ ',cat('(N = ',bign,')'));
run;

Is there a way to create another criteria that tells SAS to return nothing if there are no observations in the dataset? Thank you, any help is much appreciated!

One easy solution to prevent reusing a data set is to delete it before the new version should be created.

Assuming that the data set you may have issues with is the set DISCRETE as the output from Proc Summary then adding this somewhere before the proc summary step:

proc datasets library=work;
delete discrete;
run;

would prevent re-use. If the data set does not exist there is no error but you do get messages like:

NOTE: The file WORK.DISCRETE (memtype=DATA) was not found, but appears on a DELETE statement.

in the log.

The code for checking 1) existence and 2) numbers of observations and handling the result is a bit longer.

More specific details about which sets to ignore would be helpful.

Re: Macro issue when dataset has 0 observations

Reeza — Thu, 11 Oct 2018 23:42:56 GMT

See the example macros here, to extend your macro to appropriately handle the 0 case scenario.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Most likely Example 13 or a combo of 8/9 depending on what exactly you're trying to do.

Re: Macro issue when dataset has 0 observations

s_lassen — Fri, 12 Oct 2018 08:28:40 GMT

Instead of using the DATASETS procedure, I would recommend the resurrected DELETE procedure. That way, you do not get the library listing, and PROC DELETE is also faster, as it does not scan the whole library.

Re: Macro issue when dataset has 0 observations

kmardinian — Fri, 12 Oct 2018 15:20:27 GMT

If I were to use this code from Example 13:

data one;
x=1;
run;
data two;
stop;
run;

%macro drive(dsn);
%let dsid=%sysfunc(open(&dsn));
    
 %if &dsid ne 0 %then %do;
  %let cnt=%sysfunc(attrn(&dsid,nlobs));
  %let rc=%sysfunc(close(&dsid));
   %if &cnt ne 0 %then %do;
    proc print data=&dsn;
     title "This is data from data set &dsn"; 
    run;
   %end;
   %else %do;
    data _null_;
     title;
     file print;
     put _page_;
     put "Data set &dsn is empty.";
    run;
   %end;
 %end;
 %else %put &dsn cannot be open.;

How would i then transition into my own macro? I'm sorry, I'm new to using macros so am unfamiliar with some of the concepts

Re: Macro issue when dataset has 0 observations

kmardinian — Fri, 12 Oct 2018 15:21:57 GMT

Would it work this way?

%macro num (vorder, var);

data &dsn;
set t5;
if &var ne .;

output data=final_&vorder;
run;

proc summary data=final_&vorder completetypes chartype missing noprint;
class outcome trt;
types outcome*trt;
output out=discrete;
run;

Re: Macro issue when dataset has 0 observations

Reeza — Fri, 12 Oct 2018 15:37:28 GMT

You'd put your code in between the %else %do/%end section to allow you to have that code run. Read the detailed explanation of the macro below, that explains it step by step.

Re: Macro issue when dataset has 0 observations

Tom — Fri, 12 Oct 2018 16:00:08 GMT

@kmardinian wrote:

Hi, I created this macro below and have been running it with datasets that contain observations, but as soon as one dataset has 0 observations that fit the criteria, it seems that the macro spits out numbers from the dataset that was run previously. Here is my code below

....

You can test in the data step that is filtering the data and set a macro variable that you can use to control what code to run.

I simple way is to use WHERE instead of IF and the END= option on the SET statement.

data final_&vorder;
  if eof then call symputx('nobs',_n_-1);
  set t5 end=eof;
  where &var ne .;
run;

Make sure to put the CALL SYMPUTX() call BEFORE the SET statement. SAS data steps normally stop when they read past the end of the input data. So for an empty input set the statements after the SET statement never run.

You could then use the value of &NOBS to control your code.

%if &nobs > 0 %then %do;
* code to process the data ;
%end;
%else %do;
* code to run when input set is empty ;
%end;

Re: Macro issue when dataset has 0 observations

kmardinian — Fri, 12 Oct 2018 16:17:19 GMT

Thank you so much! I appreciate all the help, this definitely help clarify a lot of things. It's working, I ended up using the NOBS option!

%if &nobs > 0 %then %do;
* code to process the data ;
%end;
%else %do;
* code to run when input set is empty ;
%end;