topic Re: Converting date from " DATETIME20." format to SAS date format in SAS Programming

Converting date from " DATETIME20." format to SAS date format

TZaihra — Thu, 29 Aug 2013 18:43:19 GMT

Dear All,

I have a data which has date in the DATETIME20. format. I need date in sas format so that I can used a conditioning statement in my code for extracting some variables: for instance I want to use

WHERE INTNX("MONTH",B.RECRUITMENT_DT,-3,"SAME") <= datejul(input((a.DTSERV),5.))<= b.RECRUITMENT_DT

group by b.nam

Now when I try to run the below code it doesn't work, I would really appreciate any help.

Thanks

Tasneem

data map2.bill;

set map2.bill ;

xdtserv=input(dtserv, 5.); /*character to numeric*/

xxdtserv=datejul(xdtserv); /*numeric to datesas*/

format xxdtserv date9.;

run;

proc sort data=map2.bill;by nam xxdtserv;run;

Re: Converting date from " DATETIME20." format to SAS date format

Vince28_Statcan — Thu, 29 Aug 2013 19:05:08 GMT

Lookup for DATEPART function (resp. timepart).

data _null_;

dt='01JAN2011'd;

dttm=input('01JAN2011:00:00:00', datetime18.);

dtpt = datepart(dttm);

if dt=dtpt then put "Hello World!";

run;

Re: Converting date from " DATETIME20." format to SAS date format

TZaihra — Thu, 29 Aug 2013 19:13:57 GMT

Just to confirm that I understand correctly, so what you are suggesting is that

WHERE INTNX("MONTH",B.RECRUITMENT_DT,-3,"SAME") <= datepart(input((a.DTSERV),5.))<= b.RECRUITMENT_DT

and it should work, yeah?

Re: Converting date from " DATETIME20." format to SAS date format

Vince28_Statcan — Thu, 29 Aug 2013 19:40:58 GMT

No, datepart is meant to be applied directly to a datetime value. Typically, datetimes are much larger than the 10000-99999 range in numeric value.

As I couldn't tell which one of your variables were datetime20., I only provided a mean to extract the date from a datetime value.

I don't quite see where you have a datetime value in your dates. Could you provide an example both variables (dtserv before the input statement preferably) as well as their formats and informats?

Re: Converting date from " DATETIME20." format to SAS date format

Vince28_Statcan — Thu, 29 Aug 2013 19:50:40 GMT

I assume a.dtserv is your datetime variable.

Since it is already a datetime20. formatted variable, it means that it is stored as numeric already. Thus, using something like input(a.dtserv, 5.) only truncates the numeric representation of your datetime variable making it lose all of it's meaning.

If that is the case, then you simply want to use datepart(a.dtserv). That will return you a roughly 5 digit numeric value representing a date.

I can't provide better help without details on your variables or at least examples of their unformated and formated values.

Re: Converting date from " DATETIME20." format to SAS date format

TZaihra — Thu, 29 Aug 2013 19:51:03 GMT

The DTSERV variable looks like 05OCT2009:00:00:00 in SAS file and it's format is DATETIME20.

What I am doing is extracting let's say how many drugs were dispensed between the recruitment date and the service date and for that I use the following logic gate:

WHERE INTNX("MONTH",B.RECRUITMENT_DT,-3,"SAME") <= datejul(input((a.DTSERV),5.))<= b.RECRUITMENT_DT

Re: Converting date from " DATETIME20." format to SAS date format

Vince28_Statcan — Thu, 29 Aug 2013 19:53:28 GMT

We replied roughly at the same time. Based on this new information, then my previous post (#4) is your desired approach.

WHERE INTNX("MONTH",B.RECRUITMENT_DT,-3,"SAME") <= datepart(a.DTSERV)<= b.RECRUITMENT_DT

For format datetime20. to be possible, the value has to already be numeric, thus inputing with 5. format only truncates the values killing its representation.

Re: Converting date from " DATETIME20." format to SAS date format

TZaihra — Fri, 30 Aug 2013 02:03:30 GMT

Hi Vince,

Thanks a lot for your guidance and help, I really appreciate it. However, if I do as you suggested above I end up getting following message wit a table which has no values..

PROC SQL;

CREATE TABLE HOSPITAL3_10 AS

SELECT distinct a.NAM, COUNT(distinct a.dtserv) AS HOSPITAL3

FROM MAP2.BILL as a,MAP.final_pool_10patients_1 as b

WHERE a.NAM=b.NAM and a.ESTABL IN ( "0xxx0","0xxx2")

and INTNX("MONTH",B.RECRUITMENT_DT,-3,"SAME") <= datepart(a.DTSERV)<= b.RECRUITMENT_DT

group by b.nam

ORDER BY b.NAM;

NOTE: The query requires re merging summary statistics back with the original data.

NOTE: Table WORK.HOSPITAL3_10 created, with 0 rows and 2 columns.

I would really appreciate any suggestions.

Re: Converting date from " DATETIME20." format to SAS date format

Vince28_Statcan — Fri, 30 Aug 2013 11:41:53 GMT

It is extremely difficult to provide data logic support without more details on your data.

The only thing I can think of that I would investigate is the interaction between select distinct a.nam and the group by statement. The fact that you group by b.nam and have the condition a.nam=b.nam should already alleviate the need for "distinct". Furthermore, if you have a distinct statement there, it means that your data might have multiple dtserv for a given nam. In that case, the distinct statement might have a bad interraction retaining only the first dtserv which may very well occur before or after the recruitment_dt?

Anyway until you provide a small example of data (nam and establ don't have to be the same but using dates as they are in your data for the example would be very helpful), I can't be of much more help than say try to remove the distinct from distinct a.nam

Vincent

Re: Converting date from " DATETIME20." format to SAS date format

Tom — Fri, 30 Aug 2013 12:05:08 GMT

If the variable "looks like" 05OCT2009:00:00:00 and has a DATETIME20 format then the value should be the number of seconds since 1/1/1960. To convert it to a date use the DATEPART() function.

data _null_;

dt = '05OCT2009:00:00:00'dt ;

d= datepart(dt);

put dt= dt datetime20. ;

put d= d date9. ;

run;

dt=1570320000 05OCT2009:00:00:00

d=18175 05OCT2009

Re: Converting date from " DATETIME20." format to SAS date format

TZaihra — Sun, 01 Sep 2013 19:55:56 GMT

Thanks Tom