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

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

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;


Accepted Solutions
Solution
‎08-29-2013 03:50 PM
Super Contributor
Posts: 339

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

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.

View solution in original post


All Replies
Super Contributor
Posts: 339

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

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;

Contributor
Posts: 26

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

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?

Super Contributor
Posts: 339

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

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?

Solution
‎08-29-2013 03:50 PM
Super Contributor
Posts: 339

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

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.

Contributor
Posts: 26

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

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

Super Contributor
Posts: 339

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

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.

Contributor
Posts: 26

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

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.

Super Contributor
Posts: 339

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

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

Super User
Super User
Posts: 6,502

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

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

Contributor
Posts: 26

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

Thanks Tom

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 16062 views
  • 3 likes
  • 3 in conversation