BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TZaihra
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

10 REPLIES 10
Vince28_Statcan
Quartz | Level 8

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;

TZaihra
Fluorite | Level 6

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?

Vince28_Statcan
Quartz | Level 8

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?

Vince28_Statcan
Quartz | Level 8

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.

TZaihra
Fluorite | Level 6

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

Vince28_Statcan
Quartz | Level 8

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.

TZaihra
Fluorite | Level 6

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.

Vince28_Statcan
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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