View previous topic :: View next topic
|
Author |
Message |
jm_green84
New User
Joined: 13 Dec 2006 Posts: 11 Location: ATL
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
jm_green84
New User
Joined: 13 Dec 2006 Posts: 11 Location: ATL
|
|
|
|
Its format is DATE |
|
Back to top |
|
|
cpuhawg
Active User
Joined: 14 Jun 2006 Posts: 331 Location: Jacksonville, FL
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
jm_green84
New User
Joined: 13 Dec 2006 Posts: 11 Location: ATL
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
jm_green84
New User
Joined: 13 Dec 2006 Posts: 11 Location: ATL
|
|
|
|
I got it to work, thanks!! |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Glad to hear it is working -- could you let us know the solution in case anyone else has the same problem? |
|
Back to top |
|
|
|