topic Re: Call Execute not inserting rows into dataset if noobs=0 in SAS Programming

Call Execute not inserting rows into dataset if noobs=0

elwayfan446 — Mon, 24 Sep 2018 15:58:51 GMT

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;

Re: Call Execute not inserting rows into dataset if noobs=0

Kurt_Bremser — Mon, 24 Sep 2018 16:06:13 GMT

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.

Re: Call Execute not inserting rows into dataset if noobs=0

mkeintz — Mon, 24 Sep 2018 16:08:30 GMT

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:

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

Re: Call Execute not inserting rows into dataset if noobs=0

elwayfan446 — Mon, 24 Sep 2018 16:26:00 GMT

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