Using SAS/ACCESS to Teradata to create multiple derived tables

I need to create a matrix type calendar query that will replace rows that have zero value with 2 as the value in the summary column. This query is based on a file that runs monthly and daily. The file doesn't run on weekends or holidays.  In my query, The max_month column values are compared with the summary column values. If there are missing months in the summary column than the missing values are replaced with zeros. The last step is to output the data set to a text file.  My query isn't  outputting any results.
 
In the attachment, there is a text file name tran_sumary and an excel file name month.xls.  The tran_sumary file is a data file and month.xls is an example of what my query results need to print
 
 
create table xxx as 
select  * from connection to teradata
   (SELECT
       FROM
       b.LD_T,
       b.K_KY

/* create derived table b  and select the columns– extracts K_KY dates in the format of example SEP and print the K_KY largest count - b(<column list>)*/

    (select
       b.LD_T,
       b.K_KY
        cast(cast( b.K_KY as char(3)) as date format 'MMM')  AS MAX_MNTH,
        cast(cast(b.K_KY as char(8)) as date format 'yyyymmdd')  AS tran_dt
        from dy b
        Group BY 1,2
         Having Count(*) >1;)  AS b(LD_T,  MAX_MNTH, tran_dt)

/* create derived table c and select the columns – extracts sumary_end-dt dates in the format for example SEP and print the sumary_end-dt largest  count b(<column list>)*/ */

       (SELECT
          FROM
            c.sumary_end_dt
        ( SELECT
            c.sumary_end_dt
             cast(cast( a.sumary_end_dt as char(3)) as date format 'MMM')  AS 
             summary,
             FROM x.sumy_dt c
             Group BY 1,2
             Having Count(*) >1;) AS c(summary, summary_dt)

/* LEFT JOIN tables b and c and replace null with zero in the summary column when 60 days (MDIFF) are missing; create derived table o and select the columns o<column list)*/

    LEFT JOIN 
      (SELECT
           b.LD_T,  
           b.MAX_MNTH, 
           b.tran_dt,
           c.summary,
           c.summary_dt,

            ZEROIFNULL(MDIFF( c.summary, 60 , b.tran_dt)) AS diff1,
       
              CASE
              (WHEN  diff1=0  then '2'  ELSE ' '
              END)  AS diff

              FROM  b
              GROUP BY 1,2,3,4,5
       ) AS  o ON  b.MAX_MNTH=c.summary (LD_T, MAX_MNTH,summary, 
                                                          tran_dt, summary, summary_dt )
         ORDER BY 2;
     );
quit;
/*Output  data set toText File */
data _null_ ;          
    set ssd.diff ; 
    FILE  diff.txt' ;     
    PUT diff;
run;

Open in new window

tran_sumary.txt
month.xlsx
dfn48Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dfn48Author Commented:
close
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dfn48Author Commented:
close
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Statistical Analysis System (SAS)

From novice to tech pro — start learning today.