topic Re: Create new variable based on two date variables matching in SAS Procedures

Create new variable based on two date variables matching

HyunJee — Wed, 14 Dec 2011 20:42:14 GMT

I have two dates:

Entry Date

Given Date

I want to create a new variable, date_match = 1 when entry_date = given_date

I have been using the following code;

if entry_date = given_date then date_match = 1;

but the date_match variable comes up as missing.

I am doing something incorrectly, but am not sure what.

Thank you for any assistance you can provide.

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

I have attached some of the date data. There are records where the dates match and other where they do not. Thank you.

Create new variable based on two date variables matching

art297 — Wed, 14 Dec 2011 22:36:48 GMT

There are a number of reasons why the value would come up missing. See if any of the following might apply:

You misspelled on or both of the variable names

You don't have any records where they match

One of the variables is a SAS date and the other is a datetime

One or both variables are something other than SAS date variables

Does your log provide any clues?

And, if you want to have zeros and ones as values, once you identify the source of your current problem, you can simplify the statement with:

date_match=entry_date eq given_date;

Create new variable based on two date variables matching

Linlin — Wed, 14 Dec 2011 22:37:31 GMT

please make sure the two date variables are not defined as character variables.

when entry_date not equal given_date , date_match will come up as missing;

Create new variable based on two date variables matching

UrvishShah — Wed, 14 Dec 2011 22:46:23 GMT

May be your data contains white space between two dates values

Try following

data test;

input entry_date ddmmyy10. +1 given_date ddmmyy10.;

cards;

01/10/2000 01/10/2000

01/11/2001 01/12/2003

;

Make sure that your data not contains white space and if it contains space then make sure that you include space while reading data

data test;

set test;

if entry_date = given_date then date_match = 1;

run;

If you try the above code , you'll get your output

Regards

Create new variable based on two date variables matching

HyunJee — Thu, 15 Dec 2011 13:40:17 GMT

The log does not show any errors, which causes me to be puzzled as to why the code is not working.

Both dates are in the "mmddyy10." format according to the proc contents.

I used datdif to calculate the number of days difference between the two dates and was able to get the results I needed, though I still would like to be to create a variable that simply identifies if they are different.

Would it be helpful to change the formatting of the dates?

Create new variable based on two date variables matching

HyunJee — Thu, 15 Dec 2011 13:40:46 GMT

They are both number variables in the mmddyy10. format

Re: Create new variable based on two date variables matching

Linlin — Thu, 15 Dec 2011 13:43:53 GMT

Can you provide us a sample of the data you work with?

Re: Create new variable based on two date variables matching

Linlin — Thu, 15 Dec 2011 13:58:53 GMT

data have;

informat entry_date given_date mmddyy10.;

format entry_date given_date mmddyy10.;

input entry_date given_date;

date_match=entry_date eq given_date;

cards;

12/1/2011 12/20/2011

12/15/2011 12/15/2011

;

proc print ;run;

Re: Create new variable based on two date variables matching

Tom — Thu, 15 Dec 2011 14:19:23 GMT

If your dataset is small (less than a couple of hundred obs) then print it out and check if any dates actually match.

If it is large here is a trick to see if you can find examples of matches or non matches.

title1 'Matches';

proc print data=have (obs=10) ;

where entry_date = given_date ;

run;

title1 'Non-Matches';

proc print data=have (obs=10) ;

where entry_date ne given_date ;

run;

title1 ;

Re: Create new variable based on two date variables matching

ballardw — Thu, 15 Dec 2011 22:52:23 GMT

Try

date_match= (entry_date eq given_date);

I use this often. You will get a 0 for date_match when not equal.

Create new variable based on two date variables matching

HyunJee — Fri, 16 Dec 2011 15:40:54 GMT

I have attached some of the data. Thanks.

Create new variable based on two date variables matching

art297 — Fri, 16 Dec 2011 15:48:23 GMT

I don't see any attached data.

Create new variable based on two date variables matching

art297 — Fri, 16 Dec 2011 15:52:27 GMT

I just noticed the spreadsheet now attached to your original post. Entry_Date is a datetime variable, while Given_Date is a date variable.

Change your test to

date_match=datepart(entry_date) eq given_date;

Re: Create new variable based on two date variables matching

VX_Xc — Fri, 16 Dec 2011 23:52:54 GMT

First save your excel file type as CSV (comma delimited) file type to you desktop(or other folder).

Second Run below;

Data datedata;

Infile 'C:\Desktop\Date data.csv' DLM = ',' FIRSTOBS = 2 ;

Input Given_Date MMDDYY10. Sex $ Vaccine :$20. Entry_Date MMDDYY10.;

Match_Date = Given_Date - Entry_Date + 1 ;

FORMAT Given_Date Entry_Date MMDDYY10.;

run;

Check;

TITLE;

proc print data = datedata NOOBS;

run;

Given_Date	Sex	Vaccine	Entry_Date	Match_Date
12/01/2009	M	DTaP	12/01/2009	1
12/01/2009	M	Hiberix	12/01/2009	1
12/01/2009	F	H1N1 Sanofi(6-35m)	12/02/2009	0
12/01/2009	M	H1N1 Nov 4y and up	12/01/2009	1
12/01/2009	M	DTaP-HepB-IPV	12/01/2009	1

Re: Create new variable based on two date variables matching

art297 — Fri, 16 Dec 2011 23:59:54 GMT

You may have missed my last post:

I just noticed the spreadsheet now attached to your original post. Entry_Date is a datetime variable, while Given_Date is a date variable.

Change your test to

date_match=datepart(entry_date) eq given_date;

Re: Create new variable based on two date variables matching

VX_Xc — Sat, 17 Dec 2011 00:06:55 GMT

Nope, when you convert it to CSV it does not care about the underlying format. But only cares about what it shows in the cell, i.e. what you see is what you get kinda thing.

Re: Create new variable based on two date variables matching

art297 — Sat, 17 Dec 2011 00:19:38 GMT

Sorry, I had thought you were the original poster. The problem is simply that the OP is trying to compare a date with a datetime. Yes, losing that distinction by converting the file is always a possibility, but easier to solve by simply using the correct functions.

Create new variable based on two date variables matching

VX_Xc — Sat, 17 Dec 2011 00:24:14 GMT

Yes you are right. Your way would actually be much better way as it would work for more general cases.

Create new variable based on two date variables matching

VX_Xc — Sat, 17 Dec 2011 00:25:32 GMT

Can't access the link... It says I'm not allowed...

Re: Create new variable based on two date variables matching

art297 — Sat, 17 Dec 2011 00:31:09 GMT

The link wasn't intended and has since been removed.