BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dfn
Calcite | Level 5 dfn
Calcite | Level 5

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;
 
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Your TD code does not look like valid code.  Can you run that code on your TD server using Teradata SQL assistant?

IN general an SQL query should be of the form

select ....
from ...
where ...
group by ....
having ....
order by ....

Where just the SELECT and FROM clauses are required.

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

Your code does not look valid. If you want to run a pass-thru query to create a SAS dataset it should look like this:

create table XX as
select * from connection to teradata
(  
....teradata code goes here....
);

But in addition the code you posted does not look valid at all. It has many extra semi-colons in the middle of it and in general seems to have many extra random select clauses.  Re formatting it to use a consistent style would help make it more readable.

 

If you want help with writing the code you should post some example input and result data in the form of SAS data steps so that others can create sample data to test your summary logic.  If you need help with Teradata specific syntax like the ROW_NUMBER() function or the PARTITION BY keyword then a SAS forum is not the right place.

dfn
Calcite | Level 5 dfn
Calcite | Level 5
I will reformat it. Yes, there is a lot wrong with my query syntax and I was hoping someone will help me because I am very new to sas access to Teradata
dfn
Calcite | Level 5 dfn
Calcite | Level 5
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;

 

dfn
Calcite | Level 5 dfn
Calcite | Level 5
I have reformatted the teradata query . Can you tell me why my derived tables aren't working? I am having problem with the Left join and the derived table o
Tom
Super User Tom
Super User

Your TD code does not look like valid code.  Can you run that code on your TD server using Teradata SQL assistant?

IN general an SQL query should be of the form

select ....
from ...
where ...
group by ....
having ....
order by ....

Where just the SELECT and FROM clauses are required.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1248 views
  • 0 likes
  • 2 in conversation