- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you! I was about ready to throw my computer out the window. What a simple thing to forget...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.