Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Comparing a date read in from a file to a date on DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
jm_green84

New User


Joined: 13 Dec 2006
Posts: 11
Location: ATL

PostPosted: Fri Feb 06, 2009 9:16 pm    Post subject: Comparing a date read in from a file to a date on DB2
Reply with quote

Does anyone know if you have to have dates in a certain format to perform calculation on them in SAS?

I have two dates, one that I'm reading from a file thats in the following format '20090129' and I'm reading this date in as YYMMDD08., and afterward using a format statement to format it as FORMAT DATE1 YYMMDDN8.;


Then My second date I'm reading in, comes from a query in SAS that read in DATE2. I then format it as FORMAT DATE2 YYMMDDN8;


And in SAS I do something similar to this:

If DATE1 <= DATE2 Then output;


It then outputs the date even if the DATE1 is greater than DATE2.

Is there a certaining way you have to go about comparing a date read from DB2 to a date read in from a file?
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Feb 06, 2009 10:20 pm    Post subject:
Reply with quote

The format of the date returned by a select in DB2 depends on the default format set when DB2 was installed or the format you requested in the select.
Back to top
View user's profile Send private message
jm_green84

New User


Joined: 13 Dec 2006
Posts: 11
Location: ATL

PostPosted: Fri Feb 06, 2009 10:24 pm    Post subject:
Reply with quote

Its format is DATE
Back to top
View user's profile Send private message
cpuhawg

Active User


Joined: 14 Jun 2006
Posts: 331
Location: Jacksonville, FL

PostPosted: Fri Feb 06, 2009 10:35 pm    Post subject: Reply to: Comparing a date read in from a file to a date on
Reply with quote

You need to convert your YYYYMMDD dates to a SAS date value prior to the comparison. You can then compare the variables for greater than or less than. Check out this example:

Code:

  DATA TEST;                               
       INPUT @01 DATE1   $CHAR8.           
             @01 DATE2   YYMMDD8.;         
   CARDS;                                 
20090206                                   
20080206                                   
  PROC PRINT DATA=TEST;                   


Now the DATE2 variable contains the SAS date value.

Here is the printed output:

Code:

Obs     DATE1      DATE2   
                           
 1     20090206    17934   
 2     20080206    17568   
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Fri Feb 06, 2009 10:36 pm    Post subject:
Reply with quote

SAS dates are stored internally as the number of days since January 1, 1960 ... currently between 17000 and 18000. They are stored this way no matter what format you read them in as, and no matter what the output FORMAT is. After you've read in your data, issue this command in SAS
Code:
PROC CONTENTS DATA=WORK._ALL_ ;
which will tell you information about each variable. Make sure the dates are all numeric and have date formats associated with them. I've seen cases where character strings were compared to dates and 17845 is ALWAYS going to be less than 20090206 (when the character is converted to a number).
Back to top
View user's profile Send private message
jm_green84

New User


Joined: 13 Dec 2006
Posts: 11
Location: ATL

PostPosted: Sat Feb 07, 2009 12:14 am    Post subject:
Reply with quote

I did the proc content, got the below. they are all the same format but they are still not comparing correctly.


# Variable Type Len Format Informat
11 EFBEGDT Num 8 DATE9. DATE9.
12 EFENDDT Num 8 DATE9. DATE9.
4 FILEDATE Num 8 DATE9.

my code:
Code:

IF FILEDATE >= EFBEGDT AND FILEDATE <= EFENDDT THEN OUTPUT COMPARE;
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Sat Feb 07, 2009 12:18 am    Post subject:
Reply with quote

Try placing this just in front of your IF statement -- it should print the first 10 values for the 3 fields to find out what they look like.
Code:
IF _N_ <= 10 THEN DO; FILE PRINT; PUT EFBEGDT= DFENDDT= FILEDATE= ; END;
Back to top
View user's profile Send private message
jm_green84

New User


Joined: 13 Dec 2006
Posts: 11
Location: ATL

PostPosted: Sat Feb 07, 2009 3:08 am    Post subject:
Reply with quote

I got it to work, thanks!!
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Sat Feb 07, 2009 7:26 pm    Post subject:
Reply with quote

Glad to hear it is working -- could you let us know the solution in case anyone else has the same problem?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us