SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
claforest1
Fluorite | Level 6

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

3 REPLIES 3
Reeza
Super User

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
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1440 views
  • 2 likes
  • 3 in conversation