DATA Step, Macro, Functions and more

How do I read empty dataset

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How do I read empty dataset

 

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.

 


Accepted Solutions
Solution
‎09-14-2017 02:13 AM
Super User
Posts: 2,512

Re: How do I read empty dataset

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

Re: How do I read empty dataset

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;
PROC Star
Posts: 1,471

Re: How do I read empty dataset

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.

Contributor
Posts: 29

Re: How do I read empty dataset

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;

Contributor
Posts: 47

Re: How do I read empty dataset

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.

 

 

Super Contributor
Posts: 285

Re: How do I read empty dataset

Posted in reply to ErikLund_Jensen
Solution
‎09-14-2017 02:13 AM
Super User
Posts: 2,512

Re: How do I read empty dataset

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

Frequent Contributor
Posts: 118

Re: How do I read empty dataset

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 347 views
  • 4 likes
  • 8 in conversation