Problem with DATEPART function

[RPYee]

I have two queries.  In the first query, two tables are connected:

t1.tracking = t2.tracking

t1.shp_date=t2.shp_date (formatted as MMYYDDS8)

There is a computed column that uses DATEPART(t1.shp_date) and formats as MONYY5.  I want to be able to pivot on the Month/Year for future summarizations.  The computed column result is exactly what I want.  When shp_date shows "06/01/17", my computed column shows JUN17.

In the second query, I am ONLY using table t1.  I am still requesting t1.shp_date and still needing the computed column but now ALL dates are resulting in "JAN60".  I've tried everything I can think of short of connecting the other table that I do not need at this point. I would rather not do a left join on a table I don't really need.  If someone knows of a way around it, I'd appreciate your input. 

FYI, I only use the EG GUI, no code experience.

Thanks, in advance, for your help!

Rita Yee

FedEx Express, Global Trade Services

[Kurt_Bremser]

Please post both SQL query codes. Follow the advice in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce for posting code.

[PaigeMiller]

Did you actually look at the table t1 before this second query? It sounds like you have a value of zero, and so datepart(0) will give JAN60 as the formatted result.

If that's not it, show us a representative portion of your data in t1, and show us your code.

[RPYee]

I guess I didn't do as good as I thought at showing that the table (t1) is exactly the same in both queries.  There are no missing dates as the results are limited by the same date range.  I just cannot fathom why the computed column works in one query and not in the other. The only difference in the two queries is that the main table is linked to only an imported reference table in one query (computed doesn't work) and has an additional table joined in the other (computed does work).  I am referencing the exact same column as the source of the computed field in both queries.  

COMPUTED FIELD WORKS:

PROC SQL;
   CREATE TABLE WORK.'GSP CLR WDLS Origin Country'n AS 
   SELECT DISTINCT t1.shp_dt FORMAT=MMDDYYS8. AS shp_dt, 
          t1.shp_trk_nbr, 
          /* CatCause */
            (t2.sqi_grp_ctg_cd || t2.sqi_grp_caus_cd) LABEL="CatCause" AS CatCause, 
          /* MonthYr */
            (Datepart(t1.shp_dt)) FORMAT=MONYY5. LABEL="MonthYr" AS MonthYr, 
          t1.orig_cntry_cd, 
          t2.sqi_grp_cls_cd
      FROM SCANPRST.express_volume_visibility t1, SCANPRST.exprs_vol_vsbty_esqi_enhmt t2
      WHERE (t1.shp_trk_nbr = t2.shp_trk_nbr AND t1.shp_dt = t2.shp_dt) AND (t1.orig_cntry_cd IN
           ('AF','AG','AI','AM','AN','AO','AS','AZ','BA','BD','BF','BG','BI','BJ','BN','BO','BT','BY','BZ','CD','CG',
           'CI','CK','CM','CV','CY','DJ','DM','DZ','EC','EG','ER','ET','FJ','FM','FO','GA','GE','GF','GH','GI','GL','GM'
           ,'GN','GP','GR','GY','HN','HR','HT','ID','IL','IQ','IS','IT','JO','KE','KG','KH','KZ','LA','LB','LK','LR',
           'LS','LY','MA','MD','ME','MG','MH','MK','ML','MN','MP','MR','MS','MT','MU','MV','NC','NE','NG','NI','NP','OM'
           ,'PE','PF','PG','PK','PT','PW','PY','QA','RE','RS','RU','RW','SA','SC','SK','SN','SR','SV','TD','TG','TL',
           'TN','TO','TR','TZ','UA','UG','UZ','VI','VN','VU','WF','WS','ZW') AND t2.sqi_grp_ctg_cd IN
           ('C','O','P','R','S','T','V','W','X','Y')
            AND t2.sqi_grp_caus_cd IN
           ('50','52','55','60','63','64','73','74','80','88','OV','U','UC') AND t1.shp_dt BETWEEN '1Jun2017'd AND 
           '28Feb2018'd AND t2.sqi_grp_cls_cd IN 
           (
           'W',
           'R'
           ));
QUIT;

COMPUTED DOESN'T WORK:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_EXPRESS_VOLUME_VISIBIL AS 
   SELECT DISTINCT t1.shp_trk_nbr, 
          t1.shp_dt FORMAT=MMDDYYS8. AS shp_dt, 
          t1.orig_cntry_cd, 
          t2.COUNTRY_NM, 
          /* MonthYr */
            (DATEPART(t1.shp_dt)) FORMAT=MONYY5. LABEL="MonthYr" AS MonthYr
      FROM SCANPRST.express_volume_visibility t1
           INNER JOIN WORK.'COUNTRY NAME TABLE'n t2 ON (t1.orig_cntry_cd = t2.COUNTRY_CD)
      WHERE t1.orig_cntry_cd IN ('AF','AG','AI','AM','AO','AS','AZ','BA','BD','BF','BG','BI','BJ','BN','BO','BT','BY',
           'BZ','CD','CG','CI','CK','CM','CV','CY','DJ','DM','DZ','EC','EG','ER','ET','FJ','FM','GA','GE','GF','GH','GI'
           ,'GM','GN','GP','GR','GY','HN','HR','HT','ID','IL','IQ','IS','IT','JO','KE','KG','KH','KZ','LA','LB','LK',
           'LR','LS','LY','MA','MD','ME','MG','MH','MK','ML','MN','MP','MR','MS','MT','MU','MV','NC','NE','NG','NI','NP'
           ,'OM','PE','PF','PG','PK','PT','PW','PY','QA','RE','RS','RU','RW','SA','SC','SK','SN','SR','SV','TG','TL',
           'TN','TO','TR','TZ','UA','UG','UZ','VI','VN','VU','WS','ZW') AND t1.shp_dt BETWEEN '1Jun2017'd AND 
           '28Feb2018'd;
QUIT;

[PaigeMiller]

So my question "Did you actually look at the table t1 before this second query?" goes un-answered.

My request that you "show us a representative portion of your data in t1" has not been met.

[RPYee]

"So my question "Did you actually look at the table t1 before this second query?" goes un-answered. "

YES   t1.shp_dt comes back with exactly the same data output in both queries.  The computed column is what doesn't jive.

"My request that you "show us a representative portion of your data in t1" has not been met."

No clue how to show you this. The input table is massive.  Suggestions are appreciated.

[Tom · Accepted solution]

You need to decide if SHP_DT has datetime values (number of seconds) or date values (number of days).  

THis part of your query is treating it as if it as datetime values.

       (DATEPART(t1.shp_dt)) FORMAT=MONYY5. LABEL="MonthYr" AS MonthYr

And this part is treating it as if it as date values.

 AND t1.shp_dt BETWEEN '1Jun2017'd AND '28Feb2018'd

[RPYee]

Hi Tom,

Yes, i still hate SAS 🙂

This is a perfectly good example of why. 

So, am I to understand that DATEPART doesn't work on a Date field?  shp_dt is a date field.  In one query DATEPART works the way I want it to.  In another query, it doesn't.  DATEPART is used against the same column from the same source table.  I'm so damned confused, I just want to scream 😞

Ok, so let's just start over.  SHP_DT is a DATE field.   How do I compute a column that is ONLY Month and Year, so that I can summarize monthly totals?

Maybe I'm approaching this sideways 🙂

[Tom]

---

@RPYee wrote:

Hi Tom,

Yes, i still hate SAS 🙂

This is a perfectly good example of why. 

So, am I to understand that DATEPART doesn't work on a Date field?  shp_dt is a date field.  In one query DATEPART works the way I want it to.  In another query, it doesn't.  DATEPART is used against the same column from the same source table.  I'm so damned confused, I just want to scream 😞

Ok, so let's just start over.  SHP_DT is a DATE field.   How do I compute a column that is ONLY Month and Year, so that I can summarize monthly totals?

Maybe I'm approaching this sideways 🙂

---

It is really not hard to understand.  SAS stores dates as the number of days since 1960 and datetime as the number of seconds since 1960.  All the DATEPART() function does is divide the value by 24*60*60.

What is really frustrating is working with DBMS systems that do not understand the difference between a date and a datetime (and for some do not even understand time values). So that if you want to store a date you have decide what time during that date you need to store.

[Tom]

If you want to store a value that is only month and year you could convert it to a character string. (date step syntax)

yymm = put(date,yymmn6.);
monyy = put(date,monyy7.);

Or you could apply a format that displays it as just the month and year. 

format mydate yymmn6. ;

Or you could convert the value to a consistent day with in that month and apply the format. For example the first day of the month. (sql syntax)

intnx('month',date,0,'b') as monyy format=monyy7.

 or

mdy(month(date),1,year(date)) as monyy format=monyy7.

[RPYee]

Changed column to the datetime column instead of just the date column. It's weird that it let me use DATEPART on the date column in another query... Oh well. It's working now 🙂 Thanks, Tom!! I appreciate your help and patience with me.