topic How do I read empty dataset in SAS Programming

How do I read empty dataset

Hhh111 — Wed, 13 Sep 2017 09:09:24 GMT

Hi All,

DATA _NULL_ ;

SET TEST END=LAST;

IF LAST THEN CALL SYMPUT('TOTAL_RECORD',_N_) ;

RUN ;

Above is the sample code to calculate the number of observation for dataset Test. If the dataset contains data, then it shouldnt be a problem. But if the Test data is empty, when I read &Total_record it will prompt warning and stop the process.

WARNING: Apparent symbolic reference Total_record not resolved.

My expectation is, eventhough its empty, i still need it to have 0 value when i call &Total_record. Please help.

Thanks.

Re: How do I read empty dataset

RW9 — Wed, 13 Sep 2017 09:35:37 GMT

Avoid coding all in uppercase, its really hard to read. As for your problem you do not need to do anything, just a minor change to your code will get you the result - assume library is work and dataset is test:

data _null_;
  set sashelp.vtable (where=(libname="WORK" and memname="TEST"));
  call symput('TOTAL_RECORD',nobs);
run;

Re: How do I read empty dataset

Quentin — Wed, 13 Sep 2017 10:01:45 GMT

As @RW9 has already shown, there are better ways to get the number of records in a dataset that don't require reading every record. My favorite paper on counting records (and checking for empty datasets) is still: http://www2.sas.com/proceedings/sugi26/p095-26.pdf.

That said, it's helpful to think through why moving your CALL SYMPUT to before the SET statement would achieve what you want. The data step is a loop, which will stop when the SET statement tries to read a record but there is nothing to be read. Knowing that, and that end= variable is given a value even before the SET statement executes the first time, you could do:

data test;
  stop;
run;

data _null_;
  if last then call symputx('Total_Record',_N_-1);
  set test end=last;
run;

%put &Total_Record;

It's ofen useful to put an IF LAST block before the SET statement, even though this doesn't always feel like the 'logical' location for the code, until you think through that the statement immediately before the SET statement is the last statement that will execute.

Re: How do I read empty dataset

MINX — Wed, 13 Sep 2017 17:38:37 GMT

There are a lot of ways to check empty dataset as discussed in previous post https://communities.sas.com/t5/SAS-in-Health-Care-Related/Check-if-the-table-is-empty/td-p/6439

Below is one macro solution

%macro emptycheck(ds=);
	%global TOTAL_RECORD;
	%LET dsid=%SYSFUNC(OPEN(&ds.));
	%LET TOTAL_RECORD=%SYSFUNC(ATTRN(&dsid.,NOBS));
	%LET rc=%SYSFUNC(CLOSE(&dsid.));
	%put dsid=&dsid. TOTAL_RECORD=&TOTAL_RECORD. rc=&rc.;
	%if &TOTAL_RECORD=0 %then %do; 
		%put This dataset (&ds.) is empty and has &TOTAL_RECORD. obs;
		
	%end;
	%else %do;
		%put This dataset (&ds.) is not empty and has &TOTAL_RECORD. obs;
	%end;
%mend emptycheck;

Re: How do I read empty dataset

ErikLund_Jensen — Wed, 13 Sep 2017 19:23:50 GMT

Hi hhh111

Your problem is that the CALL SYMPUTX - statement never executes if the data set is empty, and the macro variable Total_Record is not created. That's why it says that the apparent symbolic reference is not resolved. The simple solution is to use your code as is is, and initiate the macro variable before the step, so it always exists whether the CALL SYMPUTX executes or not:

%let Total_Record = 0;
data _null_;
set test end=last;
if last then call symputx('Total_Record',_N_);
run;

If you use your code on several datasets in the same sesson, an empty data will not reset the value from the previous check, but will inherit the number of observations. You can avoid that too by initiating the macro variable to 0 before each check.

Re: How do I read empty dataset

SAS_inquisitive — Wed, 13 Sep 2017 19:49:10 GMT

See example 4 of this paper (http://www2.sas.com/proceedings/sugi24/Handson/p149-24.pdf).

Re: How do I read empty dataset

ChrisNZ — Thu, 14 Sep 2017 00:07:50 GMT

You simply need to call the symput function before the data step stops.


data TEST;
  stop;
run;

data _null_;
  call symputx('NOBS',NOBS);
  set SASHELP.CLASS(obs=1) nobs=NOBS;
run;
%put &=NOBS;

data _null_;
  call symputx('NOBS',NOBS);
  set TEST(obs=1) nobs=NOBS;
run;
%put &=NOBS;

NOBS=19
NOBS=0

Re: How do I read empty dataset

ShiroAmada — Thu, 14 Sep 2017 02:20:12 GMT

Try this one.....


proc sql noprint;
  select count(<PRIMARY KEY>) into: total_records
  from <YOUR INPUT DATASET >;
quit;

%put Total observations: &Total_records.;

Hope it helps.