Hi, I am getting an error message that the variable is not on File work.Tableranges. I modified a sas program I use thats uses the actual fiscal year dates which works just fine. I have included the code and output as well as the log with the error meesage. The modified code seems to work up to the point where the TableRanges is created. Not too sure whats happening after that point. Any suggestions would be greatly appreciated. Thanks in advance. %INCLUDE 'U:\SAS Templates\rac.sas'; LIBNAME myproj 'C:\Dps'; DATA _NULL_; fromdate = strip(put(intnx('year.4',today(),-3,'B'),yymmddn8.)); todate = strip(put(intnx('year.4',today(),-1,'E'),yymmddn8.)); CALL Symput ('fromdate', fromdate); CALL Symput ('todate', todate); RUN; DATA Dates; startday = 20040401; date = input(put(startday,8.),yymmdd8.); FORMAT date date9.; endDay = intnx("YEAR.4", today(), -2); DO WHILE(date <= endDay); OUTPUT; date = intnx("YEAR", date, 1, "SAME"); END; DROP endDay; FORMAT date yymmddn8.; RUN; startday date 20040401 20040401 20040401 20050401 20040401 20060401 20040401 20070401 20040401 20080401 20040401 20090401 20040401 20100401 20040401 20110401 20040401 20120401 PROC SQL; CREATE TABLE Dates_Info AS SELECT Dates.date, (input(substr(put(Dates.date,yymmddn8.),1,6),8.)) LABEL="FirstDay" AS FirstDay, (input(substr(put(INTNX('Year.4', Dates.date, 0,'E'),yymmddn8.),1,6),8.)) AS LastDay, (SUBSTR(put(Dates.date,yymmddn8.),3,2)) AS Class1, (SUBSTR(put(INTNX('Year.4', Dates.date, 0,'E'),yymmddn8.),3,2)) AS Class2, (case when (input(put(Dates.date,yymmddn8.),yymmddn8.)) <= 20070401 then catt('myproj.sar',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0, 'E'),yymmddn8.),3,2))) else catt('myproj.pdp',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0, 'E'),yymmddn8.),3,2))) end) AS Class format=$32., (case when (input(put(Dates.date,yymmddn8.),yymmddn8.)) = 20040401 then catt('myproj.dura',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0, 'E'),yymmddn8.),3,2))) when (input(put(Dates.date,yymmddn8.),yymmddn8.)) <= 20070401 then catt('myproj.durb',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0, 'E'),yymmddn8.),3,2))) else catt('myproj.durc',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0, 'E'),yymmddn8.),3,2))) end) AS Class3 format=$32. FROM WORK.DATES; QUIT; date FirstDay LastDay Class1 Class2 Class Class3 20040401 200404 200503 04 05 myproj.sar0405 myproj.dura0405 20050401 200504 200603 05 06 myproj.sar0506 myproj.durb0506 20060401 200604 200703 06 07 myproj.sar0607 myproj.durb0607 20070401 200704 200803 07 08 myproj.sar0708 myproj.durb0708 20080401 200804 200903 08 09 myproj.pdp0809 myproj.durc0809 20090401 200904 201003 09 10 myproj.pdp0910 myproj.durc0910 20100401 201004 201103 10 11 myproj.pdp1011 myproj.durc1011 20110401 201104 201203 11 12 myproj.pdp1112 myproj.durc1112 20120401 201204 201303 12 13 myproj.pdp1213 myproj.durc1213 PROC SQL; CREATE TABLE Current AS SELECT MAX(Dates_Info.LastDay) AS MAX_of_date FROM WORK.Dates_Info; QUIT; DATA Current; SET Current; MAX_of_date = MAX_of_date || '01'; RUN; DATA Current; SET Current; FirstDay = input(strip(substr(put(intnx('Month',input(put(MAX_of_date,8.),yymmdd8.),1,'B'),yymmddn8.),1,6)),6.); RUN; MAX_of_date FirstDay 20130301 201304 DATA ab; SET Current; LastDay = input(strip(substr(put(intnx('Month',today(),-1,'E'),yymmddn8.),1,6)),6.); Class = 'rac.Cend'; Class3 = 'rac.Nonj'; RUN; MAX_of_date FirstDay LastDay Class Class3 20130301 201304 201501 rac.Cend rac.Nonj DATA TableRanges (DROP=date Class1 Class2 MAX_of_date); SET dates_info ab; RUN; FirstDay LastDay Class Class3 200404 200503 myproj.sar0405 myproj.dura0405 200504 200603 myproj.sar0506 myproj.durb0506 200604 200703 myproj.sar0607 myproj.durb0607 200704 200803 myproj.sar0708 myproj.durb0708 200804 200903 myproj.pdp0809 myproj.durc0809 200904 201003 myproj.pdp0910 myproj.durc0910 201004 201103 myproj.pdp1011 myproj.durc1011 201104 201203 myproj.pdp1112 myproj.durc1112 201204 201303 myproj.pdp1213 myproj.durc1213 201304 201501 rac.Cend rac.Nonj PROC SQL NOPRINT; SELECT Class INTO :tableList separated by ' ' FROM TableRanges WHERE input(substr(put(LastDay,8.),1,6),6.) >= input(substr(put(&fromDate,8.),1,6),6.) and input(substr(put(FirstDay,8.),1,6),6.) <= input(substr(put(&toDate,8.),1,6),6.); QUIT; PROC SQL NOPRINT; SELECT catx(' ','date_processed_ym between', input(substr(put(&fromDate,8.),1,6),6.),'and',input(substr(put(&toDate,8.),1,6),6.)) INTO :wherelist separated by ' OR ' FROM TableRanges WHERE input(substr(put(LastDay,8.),1,6),6.) >= input(substr(put(&fromDate,8.),1,6),6.) and input(substr(put(FirstDay,8.),1,6),6.) <= input(substr(put(&toDate,8.),1,6),6.); QUIT; %LET wherelist = &wherelist; DATA Summary4; SET &tableList; WHERE (("&wherelist") AND (date_processed_ym between (input(substr(&fromDate,1.6),6.)) and (input(substr(&toDate,1,6),6.))) AND (sid=1)); DROP ref_num rec_id; RUN; LOG With Error Message: 109 110 %LET wherelist = &wherelist; 111 112 DATA Summary4; 113 SET &tableList; 114 WHERE (("&wherelist") 115 AND (date_processed_ym between (input(substr(&fromDate,1.6),6.)) and 115 ! (input(substr(&toDate,1,6),6.))) 116 AND (sid=1)); ERROR: Variable date_processed_ym is not on file WORK.TABLERANGES. 117 DROP ref_num rec_id; 118 RUN;
... View more
