Desktop productivity for business analysts and programmers

Problem with DATEPART function

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Problem with DATEPART function

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

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.

Accepted Solutions
Solution
‎04-10-2018 11:07 AM
Super User
Super User
Posts: 7,860

Re: Problem with DATEPART function

[ Edited ]

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

 

View solution in original post


All Replies
Super User
Posts: 9,611

Re: Problem with DATEPART function

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 2,661

Re: Problem with DATEPART function

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.

--
Paige Miller
Contributor
Posts: 34

Re: Problem with DATEPART function

[ Edited ]
Posted in reply to PaigeMiller

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;
___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Respected Advisor
Posts: 2,661

Re: Problem with DATEPART function

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.

--
Paige Miller
Contributor
Posts: 34

Re: Problem with DATEPART function

Posted in reply to PaigeMiller

"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.

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Solution
‎04-10-2018 11:07 AM
Super User
Super User
Posts: 7,860

Re: Problem with DATEPART function

[ Edited ]

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

 

Contributor
Posts: 34

Re: Problem with DATEPART function

[ Edited ]

Hi Tom,

Yes, i still hate SAS Smiley Happy

 

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 Smiley Sad

 

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 Smiley Happy

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Super User
Super User
Posts: 7,860

Re: Problem with DATEPART function


@RPYee wrote:

Hi Tom,

Yes, i still hate SAS Smiley Happy

 

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 Smiley Sad

 

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 Smiley Happy


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.

 

Super User
Super User
Posts: 7,860

Re: Problem with DATEPART function

[ Edited ]

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.
Contributor
Posts: 34

Re: Problem with DATEPART function

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 Smiley Happy Thanks, Tom!! I appreciate your help and patience with me.
___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 228 views
  • 0 likes
  • 4 in conversation