ERROR my new variable is not found in table

[claforest1]

Hi this is my first time posting. I am trying to create a new variable defining fiscal year and then creating an excel export based off of that variable. This is my code. The error message I keep received is: 

ERROR: The following columns were not found in the contributing tables: purch_fy.

libname home "/Forest/SAS Data";
libname cba4a "/CBA/staging";


%macro extract7a(date_input);

%let lst12mn = %eval(&date_input. - 1);

data ext_7a_&date_input.;
	set cba4a.abcde21_&date_input.07 (keep = apfy	
						asofdate
						apprv_dt
						purch_cnt
						purch_dt
						purch_amtgrs
						lst12mnpurch_amtgrs
						lst12mnchoff_amtgrs
						purch_amtsba
						purch_balgrs
						purch_balsba
						purch_cnt
						purchases_amt
						purchases_amt_cum
						chargeoffpringrs
						choff_balgrs
						choff_cnt
						choff_dt
												);

data ext_7a2_&date_input.;
	set ext_7a_&date_input.;
	if purch_dt < '01OCT2014'd then delete;
	if missing(purch_dt) then delete;
	length purch_fy $4;
	if '01OCT2018'd < purch_dt <= '30JUN2019'd then purch_fy = "2019";
	else if '01OCT2017'd < purch_dt <= '30SEP2018'd then purch_fy = "2018";
	else if '01OCT2016'd < purch_dt <= '30SEP2017'd then purch_fy = "2017";
	else if '01OCT2015'd < purch_dt <= '30SEP2016'd then purch_fy = "2016";
	else if '01OCT2014'd < purch_dt <= '30SEP2015'd then purch_fy = "2015";
run;

proc sql;
	create table home.portfoliochoffpurch&date_input. as select
	purch_fy,
	sum(purch_cnt) as countpurch,
	sum(choff_cnt) as countchoff,
	sum(lst12mnpurch_amtgrs) as purchamt,
	sum(lst12mnchoff_amtgrs) as choffamt
	from ext_7a_&date_input.
	group by purch_fy;
quit;


%mend;

%extract7a(2019);


data home.portfolio_7aPC;
	set	home.portfoliochoffpurch2019;
run;

proc export data=home.portfolio_7aPC outfile = "/Forest/Excel Output/portfolio_7aPC.xlsx"
	dbms = xlsx replace;
run;

[Reeza · Accepted solution]

You're referencing your original data set, not the created one. From should reference ext_7a2_&date_input.

proc sql;
	create table home.portfoliochoffpurch&date_input. as select
	purch_fy,
	sum(purch_cnt) as countpurch,
	sum(choff_cnt) as countchoff,
	sum(lst12mnpurch_amtgrs) as purchamt,
	sum(lst12mnchoff_amtgrs) as choffamt
	from ext_7a_&date_input.
	group by purch_fy;
quit;

Spoiler

---

@claforest1 wrote:

Hi this is my first time posting. I am trying to create a new variable defining fiscal year and then creating an excel export based off of that variable. This is my code. The error message I keep received is: 

ERROR: The following columns were not found in the contributing tables: purch_fy.

libname home "/Forest/SAS Data";
libname cba4a "/CBA/staging";


%macro extract7a(date_input);

%let lst12mn = %eval(&date_input. - 1);

data ext_7a_&date_input.;
	set cba4a.abcde21_&date_input.07 (keep = apfy	
						asofdate
						apprv_dt
						purch_cnt
						purch_dt
						purch_amtgrs
						lst12mnpurch_amtgrs
						lst12mnchoff_amtgrs
						purch_amtsba
						purch_balgrs
						purch_balsba
						purch_cnt
						purchases_amt
						purchases_amt_cum
						chargeoffpringrs
						choff_balgrs
						choff_cnt
						choff_dt
												);

data ext_7a2_&date_input.;
	set ext_7a_&date_input.;
	if purch_dt < '01OCT2014'd then delete;
	if missing(purch_dt) then delete;
	length purch_fy $4;
	if '01OCT2018'd < purch_dt <= '30JUN2019'd then purch_fy = "2019";
	else if '01OCT2017'd < purch_dt <= '30SEP2018'd then purch_fy = "2018";
	else if '01OCT2016'd < purch_dt <= '30SEP2017'd then purch_fy = "2017";
	else if '01OCT2015'd < purch_dt <= '30SEP2016'd then purch_fy = "2016";
	else if '01OCT2014'd < purch_dt <= '30SEP2015'd then purch_fy = "2015";
run;

proc sql;
	create table home.portfoliochoffpurch&date_input. as select
	purch_fy,
	sum(purch_cnt) as countpurch,
	sum(choff_cnt) as countchoff,
	sum(lst12mnpurch_amtgrs) as purchamt,
	sum(lst12mnchoff_amtgrs) as choffamt
	from ext_7a_&date_input.
	group by purch_fy;
quit;


%mend;

%extract7a(2019);


data home.portfolio_7aPC;
	set	home.portfoliochoffpurch2019;
run;

proc export data=home.portfolio_7aPC outfile = "/Forest/Excel Output/portfolio_7aPC.xlsx"
	dbms = xlsx replace;
run;

---

[claforest1]

Thank you! I was about ready to throw my computer out the window. What a simple thing to forget... 

[Tom]

One problem with making some many extra copies of the same data is remember what names you have given the different versions.

Your SQL step is reading the dataset that was used to create the dataset that has the new variable. Instead of reading the dataset that actually has the new variable.

data ext_7a2_&date_input.;
  set ext_7a_&date_input.;

...

from ext_7a_&date_input.