BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

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;

1 REPLY 1
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

From your SAS code piece, file WORK.TABLERANGES is sourced from a DATA step that inputs with a SET dates_info ab;  specified.  Neither of those two files has a SAS variable with the name you are getting for the SAS ERROR.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 3634 views
  • 0 likes
  • 2 in conversation