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

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
MINX
Obsidian | Level 7

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;

ErikLund_Jensen
Rhodochrosite | Level 12

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.

 

 

ChrisNZ
Tourmaline | Level 20

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

ShiroAmada
Lapis Lazuli | Level 10

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.

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
  • 7 replies
  • 4648 views
  • 5 likes
  • 8 in conversation