Hi - I am looking to list multiple values in one variable I want to include in a proc SQL table under a where statement. The code listed below did not work. What am I missing/doing wrong? Thank you!   proc sql; create table largesmall_&date_input. as select &date_input. as asofdate, mainbank, mainbankid, loansize, sum(lst12mn_count) as lst12mn_count, sum(lst12mn_grsapproval) as lst12mn_grsapproval from lenders_&date_input. where mainbankid='A363289' or 'A108430' or '6606272' or '5521965' or '7022337' or '5521965' or 'A491999' or 'A200760' or '7062401' or 'A331210' or 'A055698' or '7021510' or 'A070130' or 'A058410' or '7021510' group by 1,2,3,4 ; quit; ... View more

Hi - I am looking at bank lending data by bank within different industries. I want to create a table that has the banks with the largest proportion of their loans in one industry. I am looking for the top 20. This is the code I have and I am really stuck on how to actually calculate the industry proportion of loans by bank - the one piece I need.    It all comes from one huge database.    libname home "/Excel_Output"; libname s4a "/staging"; data industrycon_; set s4a.bank_201907 (keep = mainbank mainbankid bankstate naics_sect_cd naics_sector balance ) ; if balgrs=0 then delete; run; proc sql; create table indcon_ as select distinct mainbank, mainbankid as mainbankid, bankstate as bankstate, sum(balance) as balance_ from industrycon_ group by mainbank, mainbankid; quit; proc sql; create table indconman as select distinct mainbank, mainbankid as mainbankid, bankstate as bankstate, sum(balance) as balance_ from industrycon_ where naics_sector="Manufacturing" group by mainbank, mainbankid; quit; proc sql outobs=20; create table test as select distinct a.*,b.balgrs1_ as balgrs from indconman as a full join indcon_ as b on a.mainbank = b.mainbank order by balgrs desc; quit; ... View more

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 more