topic Re: I suspect the macro script is arranged wrongly and hence producing error in SAS Programming

I suspect the macro script is arranged wrongly and hence producing error

imdickson — Wed, 13 Mar 2019 02:02:25 GMT

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.

Re: I suspect the macro script is arranged wrongly and hence producing error

Reeza — Wed, 13 Mar 2019 02:25:11 GMT

This section is possibly an issue. You don't need to have the data set exist before for PROC APPEND to work.

Check your log to see if the error (data set not found) is printed to the log.

%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;

You do not need to conditionally do this step, the following should work just as well, note that I did not ensure that the %END were correctly selected so you'll have to refactor the code.

proc append base=stateapp data=lfconsld.&&sds&i force nowarn;
run;

@imdickson wrote:

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.

Re: I suspect the macro script is arranged wrongly and hence producing error

Astounding — Wed, 13 Mar 2019 03:14:58 GMT

This comment is heading in the right direction. You posted the log at the point where the error occurred. Equally important is the earlier section of the log when %siterate executes. That's the code that is supposed to create the missing data set.

Re: I suspect the macro script is arranged wrongly and hence producing error

andreas_lds — Wed, 13 Mar 2019 06:12:16 GMT

And while you are fixing the errors, un-nest the macro definitions. It is always a bad idea to define a macro inside another macro. All macros are always visible in the whole session.