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;
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.
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;
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?
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?
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.
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
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.
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.
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
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
Thanks Tom
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.