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

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

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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
RPYee
Quartz | Level 8

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.
PaigeMiller
Diamond | Level 26

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
RPYee
Quartz | Level 8

"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.
Tom
Super User Tom
Super User

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
Quartz | Level 8

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 🙂

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

@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
Super User Tom
Super User

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
Quartz | Level 8
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.
___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.

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
  • 10 replies
  • 18248 views
  • 0 likes
  • 4 in conversation