I am currently trying to debug a script which is giving error when i first run it. Subsequent run after first time run(with error) will not give error and hence i suspect it is due to script order/arrangement.
Let's have a look at the script:
/* %let year = %eval(%sysfunc(year("&sysdate"d)) + 1); */
/* get latest year based on workflow */
proc sql noprint;
select max(year) into :year from netcap.ForecastWorkflow;
quit;
LIBNAME lfconsld BASE "/sasdata/ncp_lf_consolidate/";
%let stepState = 1;
%let scntState = 1;
proc sql noprint;
select count(*) into :flowCount from netcap.ForecastWorkflow
where VaUpdatedDttm is null or SubmittedDttm > VaUpdatedDttm;
quit;
%macro DoOneSet(NATION, HV);
proc sql noprint;
create table xlist as
select distinct DATASET, YEAR, STATE, HvFlag
from netcap.ForecastWorkflow
where YEAR = &YEAR and
HvFlag=&HV
and
%if &NATION eq "NATION" %then %do;
state = "NATION"
%end;
%else %do;
state ^= "NATION"
%end;
;
quit;
%macro nobs(iDs);
%local dsid nObs rc;
%let dsID = %sysfunc(open(&iDs));
%if &dsID %then %do;
%let nObs = %sysfunc(attrn(&dsID,nlobsf));
%let rc = %sysfunc(close(&dsID));
%end;
%else %do;
%put WARNING: Table &iDs does not exist;
%let nObs = -1; /*Return -1 if table not exist*/
%end;
&nObs
%mend;
data _null_;
set xlist;
call symputx(compress("sds"||_n_),DATASET);
call symputx("scnt",_n_);
run;
%macro siterate(i);
%if &i EQ 1 %then %do;
%if %sysfunc(exist(lfconsld.&&sds&i)) %then %do;
data stateapp;
set lfconsld.&&sds&i;
run;
%end;
%else %do;
%put dataset not found;
%end;
%end;
%else %do;
%if %sysfunc(exist(lfconsld.&&sds&i)) %then %do;
proc append base=stateapp data=lfconsld.&&sds&i force nowarn;
run;
%end;
%else %do;
%put dataset not found;
%end;
%end;
/* %put scntState = %eval(&scntState + 1);*/
/* %if &scnt GE 1 and %nobs(lfconsld.&sds1) GT 0 %then %do;*/
/* data stateapp;*/
/* set lfconsld.&sds1;*/
/* run;*/
/* %end;*/
/* %if &scnt GE 2 %then %do;*/
/* %do i=2 %to &scnt;*/
/* %if %nobs(lfconsld.&sds1) GT 0 %then %do;*/
/* proc append base=stateapp data=lfconsld.&&sds&i force nowarn;*/
/* run;*/
/* %end;*/
/* %end;*/
/* %end;*/
%mend siterate;
%if %nobs(xlist) GT 0 %then %do;
%do i=1 %to &scnt;
%siterate(%eval(&i));
%end;
%end;
%else %do;
%goto macro_exit;
%end;
proc sql noprint;
create table summed_actual as
select period,sum(actual) as summed_actual from stateapp group by period;
quit;
proc sql noprint;
select max(period) into :max_period from summed_actual where summed_actual is not null;
quit;
proc sql noprint;
create table aggregate_actual as
select *
,(case when period <= &max_period then ACTUAL else PREDICT end) as kwh_nominal label="Nominal KWH" format=comma22.
from stateapp order by STATE, business_area, tariff_category, period;
quit;
proc sql noprint;
create table aggregate_year as
select
state,business_area,tariff_category,year(period) as year
,sum(actual) as actual_sales label="Historical Sales" format=comma22.
,sum(kwh_nominal) as yearly_sales label="Yearly Sales" format=comma22.
from aggregate_actual group by STATE, business_area, tariff_category,year;
quit;
proc sql noprint;
create table state_sales_for_year as
select state, year(period) as year, sum(actual) as sales
from stateapp group by state, year;
run;
proc sql noprint;
create table aggregate_year_tariff_proof as
select
state,business_area,year,tariff_category,yearly_sales
,sum(yearly_sales) as ba_sales_year label="Business Area Sales" format=comma22.
from aggregate_year group by STATE, business_area,year;
quit;
proc sql noprint;
create table aggregate_year_tariff as
select
state,business_area,year
,sum(yearly_sales) as ba_sales_year label="Business Area Sales" format=comma22.
from aggregate_year group by STATE, business_area,year;
quit;
proc sql noprint;
create table aggregate_business_area_tariff as
select
state,year
,sum(yearly_sales) as ba_sales_year label="State Sales" format=comma22.
from aggregate_year group by STATE,year;
quit;
data growth_year;
set aggregate_year end=last;
by state business_area tariff_category year;
yearly_growth = dif( yearly_sales );
yearly_growth_rate = dif( yearly_sales ) / lag( yearly_sales );
if first.tariff_category then do;
yearly_growth = .;
yearly_growth_rate = .;
end;
label yearly_growth = "Yearly Growth Sales";
label yearly_growth_rate = "Yearly Growth Rate";
format yearly_growth_rate percent10.2;
format yearly_growth comma22.;
run;
data growth_year_ba;
set aggregate_year_tariff end=last;
by state business_area year;
ba_yearly_growth = dif( ba_sales_year );
ba_yearly_growth_rate = dif( ba_sales_year ) / lag( ba_sales_year );
if first.business_area then do;
ba_yearly_growth = .;
ba_yearly_growth_rate = .;
end;
label ba_yearly_growth = "Yearly Growth Sales (BA)";
label ba_yearly_growth_rate = "Yearly Growth Rate (BA)";
format ba_yearly_growth_rate percent10.2;
format ba_yearly_growth comma22.;
run;
data growth_year_state;
set aggregate_business_area_tariff end=last;
by state year;
state_yearly_growth = dif( ba_sales_year );
state_yearly_growth_rate = dif( ba_sales_year ) / lag( ba_sales_year );
if first.state then do;
state_yearly_growth = .;
state_yearly_growth_rate = .;
end;
label state_yearly_growth = "Yearly Growth Sales (State)";
label state_yearly_growth_rate = "Yearly Growth Rate (State)";
format state_yearly_growth_rate percent10.2;
format state_yearly_growth comma22.;
run;
proc sql noprint;
create table DataSetOutput as
select A.year,A.State,A.business_area,A.tariff_category,A.yearly_sales as tariff_sales_year,
B.ba_sales_year as ba_sales_year,
C.ba_sales_year as state_sales_year,
D.yearly_growth as tariff_growth_year,
D.yearly_growth_rate as tariff_growth_rate,
E.ba_yearly_growth as ba_growth_year,
E.ba_yearly_growth_rate as ba_growth_rate,
F.state_yearly_growth as state_growth_year,
F.state_yearly_growth_rate as state_growth_rate,
A.actual_sales as actual_sales,
catx("_", &NATION, &HV) as fsresult_type
from aggregate_year as A
left join aggregate_year_tariff as B
on B.state = A.State and B.year = A.year and B.business_area = A.business_area
left join aggregate_business_area_tariff as C
on C.state = A.state and C.year = A.year
left join growth_year as D
on D.state = A.state and D.year = A.year and D.business_area = A.business_area and D.tariff_category = A.tariff_category
left join growth_year_ba as E
on E.state = A.state and E.year = A.year and E.business_area=A.business_area
left join growth_year_state as F
on F.state = A.state and F.year = A.year;
quit;
%if &stepState EQ 1 %then %do;
data xoutput;
set DataSetOutput;
run;
%end;
%else %do;
proc append base=xoutput data=DataSetOutput force nowarn;
%end;
%let stepState = %eval(&stepState + 1);
%macro_exit:
%mend DoOneSet;
%let flowCount = 1;
%macro main();
%if &flowCount GT 0 %then %do;
%DoOneSet("NATION", "XHV");
%DoOneSet("NATION", "HV");
%DoOneSet("STATE", "XHV");
%DoOneSet("STATE", "HV");
%end;
%else %do;
data xoutput;
set ncpva.ncp_va_forecast_yearly;
run;
%end;
%mend;
%main;
/*&_OUTPUT1*/
data OUTPUT1;
set xoutput;
run;
/*&_OUTPUT2*/
data OUTPUT2;
set state_sales_for_year;
run;
The error for first run:
MPRINT(DOONESET): ;
MPRINT(DOONESET): proc sql noprint;
MPRINT(DOONESET): create table summed_actual as select period,sum(actual) as summed_actual from stateapp group by period;
ERROR: File WORK.STATEAPP.DATA does not exist.
However, when i open stateapp work table, there is data. About 300k records in it.
So i have no clue why is it giving this error.
After the first run, if i rerun again(without closing the session), i will not get error but only warning complaining about division by zero. However, i still dont get why do i hit the error on every first run. This script is developed by someone else long long time ago.
Click image to register for webinar
