Month & Year functions 1

[irinnew]

Assuming that day of Birth might be not accurate in left hand table (as I got less records than expected) I decided to join both tables just by month and by year. I got the below log however. Does it mean I wrongly combine datepart and month/year function? Is there any syntax mistake? It does work properly when I use datepart function alone on the left like that:

Datepart(B.date_of_birth)=A.MEM_BIRTH_DT

After I included month /year like the following it stopped working properly:

52 datepart(month(B.date_of_birth))=month(A.MEM_BIRTH_DT) AND
53 datepart(YEAR(B.date_of_birth))=YEAR(A.MEM_BIRTH_DT)

************************************************************
log:
PROC SQL;
45 !
46 CREATE TABLE OUT.FOUND_HEALTH2 AS
47 (select distinct A.*, b.startdate, b.enddate, b.date_of_birth
48 FROM OUT.bc65_strangers_not_in2003 B, OUT.RGSTR_key_real A
49 WHERE b.MEMBER_LAST_NAME= a.MEM_LAST_NAME AND
50 b.MEMBER_FIRST_NAME= a.MEM_FIRST_NAME AND
51 B.sex=a.MEM_gender AND
52 datepart(month(B.date_of_birth))=month(A.MEM_BIRTH_DT) AND
53 datepart(YEAR(B.date_of_birth))=YEAR(A.MEM_BIRTH_DT)
54 );
INFO: Data set OUT.BC65_STRANGERS_NOT_IN2003.DATA is in a foreign host format. Cross Environment Data Access will be used, which
may require additional CPU resources and reduce performance.
NOTE: Invalid argument to function MONTH. Missing values may be generated.
NOTE: Invalid arguments to the DATEPART function have caused the function to return a missing value.
NOTE: Invalid argument to function YEAR. Missing values may be generated.
NOTE: Invalid arguments to the DATEPART function have caused the function to return a missing value.
NOTE: Invalid arguments to the MONTH function have caused the function to return a missing value.
NOTE: Invalid arguments to the YEAR function have caused the function to return a missing value.
NOTE: Table OUT.FOUND_HEALTH2 created, with 0 rows and 11 columns.


What I am doing wrong

Irin



--------------------------------------------------------------------------------

 

[klaz2002]

Irin,
Datepart is a function that is only used with a datetime value. Remember SAS stores Datetime values as the number of seconds from 12:00am 1/1/1960. In other words a big number. What you supply to the datepart function is at most a value of 12. Well, this doen't allow the datepart function to work as that would be the 12th second on 1/1/1960 (12:00:12).

Most of the time the error you are getting is a char value that is passed to this function.

Perhaps you can post the data values that you are testing (before any processing) so that I can give you the correct SAS test statement.
Klaz

 

[irinnew]

Klaz,

I did proc contents for both values:

Proc contents result of data warehouse table (OUT.RGSTR_key_real A)


Variable Type Len Pos Format Informat Label
---------------------------------------------------------------------------------
MEM_BIRTH_DT Num 8 0 DATE9. DATE9. MEM_BIRTH_DT



Proc contents result of SAS dataset (OUT.bc65_strangers_not_in2003 B),


Variable Type Len Pos Format Informat Label
----------------------------------------------------------------------------------

DATE_OF_BIRTH Num 8 0 DATETIME20. DATETIME20. DATE_OF_BIRTH

 

[klaz2002]

ok simple fix. You need to reverse the functions like this

old

  datepart(month(B.date_of_birth))=month(A.MEM_BIRTH_DT) AND
  datepart(YEAR(B.date_of_birth))=YEAR(A.MEM_BIRTH_DT)

new

  month(datepart(B.date_of_birth))=month(A.MEM_BIRTH_DT) AND
  YEAR(datepart(B.date_of_birth))=YEAR(A.MEM_BIRTH_DT)

Hope this helps you,
Klaz

 

[irinnew]

Klaz,

When I applied it to the following code it generate the log:

153 data equalStartdate NotequalStartdate;
154 set bc65strangers_dates;
155 by member_first_name member_last_name sex date_of_birth;
156 IF MONTH(DATEPART(startdate))=MONTH(dm_program_eff_dt) AND
157 YEAR(DATEPART(startdate))=YEAR(dm_program_eff_dt)
158 THEN output equalStartdate;
159 ELSE output NotequalStartdate;
160 run;

NOTE: Invalid argument to function MONTH at line 156 column 32.
DM_PROGRAM_EFF_DT=20JAN2002:00:00:00 DM_PROGRAM_TERM_DT=30JUN2002:00:00:00
TERM_REASON=Member doesnt wish to participate sex=F member_first_name=A member_last_name=CROY
date_of_birth=12NOV1910:00:00:00 startdate=02/16/2002 enddate=04/30/2005 FIRST.member_first_name=1
LAST.member_first_name=0 FIRST.member_last_name=1 LAST.member_last_name=1 FIRST.sex=1 LAST.sex=1
FIRST.date_of_birth=1 LAST.date_of_birth=1 _ERROR_=1 _N_=1



This is for startdate

Variable Type Len Pos Format Informat Label
__________________________________________________________________________________

startdate Num 8 8 MMDDYY10.





This is for DM_PROGRAM_EFF_DT

*****************************************************************************************

Variable Type Len Pos Format Informat Label
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
DM_PROGRAM_EFF_DT Num 8 0 DATETIME20. DATETIME20. DM_PROGRAM_EFF_DT


It looks like this case differs and I am doing wrong something again…....

 

[klaz2002]

irin,
I think you need to use the datepart function on the right side of that statement as well. From what I can tell, dm_program_eff_dt has a datetime value in it.

IF MONTH(DATEPART(startdate))=MONTH(datepart(dm_program_eff_dt)) AND
YEAR(DATEPART(startdate))=YEAR(datepart(dm_program_eff_dt))

I hope this helps.
Klaz

 

[ChrisW75]

Actually, you shouldn't be using DATEPART on STARTDATE, it isn't a datetime field, the format on it is mmddyy10.

 

[klaz2002]

Chris is right. I just looked at your error log in your may18 9:14 post. The log shows the vale in startdate and its not a datetime value.
so change the code to this:

IF MONTH(startdate)=MONTH(datepart(dm_program_eff_dt)) AND
   YEAR(startdate)=YEAR(datepart(dm_program_eff_dt))

I hope this helps you,
Klaz