Call Execute not inserting rows into dataset if noobs=0

[elwayfan446]

Hi,

I can't figure out why this code is not working.  I am trying to insert dummy rows into this data set when there are no observations found on it.  If I run the proc sql alone, it will insert the rows.  However, when I use the call execute if noobs=0, no observations are inserted.

data _null_;
set msrflow.msrflow_loansfunded_10YR_append;
if nobs=0 then do;
call execute(
'proc sql; 
	insert into msrflow.msrflow_loansfunded_10YR_append 
			(
				PRIMARY_LOAN_KEY 
				,INVESTOR_LOAN_ID 
				,SELLER_LOAN_NUMBER 
				,PNC_LOAN_NUMBER 
				,STATUS 
				,INVESTOR 
				,FUNDING_DATE 
				,NOTE_BALANCE 
				,NOTE_RATE 
				,SERVICE_FEE 
				,LTV 
				,FICO 
				,SRP_RATE 
				,SRP_AMOUNT 
				,PRODUCT 
				,ESCROW_FLAG 
				,PURPOSE 
				,TIMEFRAME 
				,PCT_TOTAL_UPB_PURCHASE 
				,PCT_TOTAL_UPB_REFI 
				,PCT_TOTAL_CNT_ESCROW 
				,PCT_TOTAL_CNT_NONESCROW 
				,WGT_AVG_10YR 
			)
		values (0,"","","","","",.,0,0,0,0,0,0,0,"","","","MTD",0,0,0,0,0)
		values (0,"","","","","",.,0,0,0,0,0,0,0,"","","","YTD",0,0,0,0,0)
		values (0,"","","","","",.,0,0,0,0,0,0,0,"","","","LTD",0,0,0,0,0)
	;
quit;'
);
end;
run;

[Kurt_Bremser]

Maxim 2: read the log. You'll find a NOTE that nobs is uninitialized. You forgot the nobs= option in the set statement, and you need to move the set statement after the end and before the run.

[mkeintz · Accepted solution]

When a set statement attempts to read beyond the end of a data set, the data step immediately stops.   With zero obs, the first SET provokes that response.   There is a way, however, to get the proper nobs:

data _null_;
  if 0 then set msrflow.msrflow_loansfunded_10YR_append nobs=nrecs;
  put nrecs=;
  if nrecs=0 then call execute ('....');
  stop;
run;

This works because:

1. The is no attempt to read beyond the end of data  ("if 0" is always false).
2. But the "nobs=..." option is evaluated prior to data reading, so NRECS will have a testable value.
3. The "stop" statement is not necessary, but a good habit for cases like this.

[elwayfan446]

Thank you both for the help.  Both solutions worked for me and were very helpful.