topic Re: Having a difficult time with a date conversion issue in SAS Programming

Having a difficult time with a date conversion issue

pasvorto — Wed, 05 Mar 2014 20:55:11 GMT

I am getting records from a customer that are characters in the format yyyymmdd (20130101). I have a date (&start) field that I need to compare to that date. If I hard code the date ('20130101') instead of using &start it works, If I try to use &start I get an error that the data fields are incompatible.

ERROR: Expression using greater than or equal (>=) has components that are of different data types.

Here is the code:

proc sql;

CREATE table ccases1a

as SELECT

a.study AS study_num, i.inst_id, a.patient AS patient_id, a.regis_dt, a.pt_sex AS sex_id, a.ethnicity AS ethnic_id

FROM alpha.rss_accrual as a

JOIN alpha.institution as i

ON a.inst_nci_id = i.nci_id

WHERE i.GROUP_ID = 1

AND a.regis_dt >= &start. /*'20130301' */ If I use the '20130301' it works

AND (i.stop_dt =. or a.regis_dt <= &end.); /* '20140301'); */

quit;

DATA _NULL_;

call symput('start',input("&st",mmddyy10.));

call symput('end', input("&en",mmddyy10.));

RUN;

I need to either convert the character date to something compatible, or convert the &start to a character date in the correct format.

Any ideas?

Re: Having a difficult time with a date conversion issue

Reeza — Wed, 05 Mar 2014 22:13:54 GMT

I recommend converting all to dates, though its not necessarily the fastest.

ie input(regis_dt, mmddyy10.) should work, you can replace the date with that formula in your comparison's above.

Re: Having a difficult time with a date conversion issue

Patrick — Wed, 05 Mar 2014 22:15:26 GMT

Assuming that regis_dt contains a string you could use:

a.regis_dt>="&start"

where

%let start=20130101;

This compares stings and not date values but should work fine for how the date is represented in these strings.

Re: Having a difficult time with a date conversion issue

Reeza — Wed, 05 Mar 2014 22:16:59 GMT

PS. Also verify how you are inputting/creating your macro variables. You've specified them as MMDDYY format not a YYYYMMDD format as noted in your question.

%let st=03012013;

%let en=03012014;

DATA _NULL_;

call symput('start',input("&st",mmddyy10.));

call symput('end', input("&en",mmddyy10.));

RUN;

%put &start ;

%put &end;

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 13:38:49 GMT

We are circling the answer, but not quite there yet.

I have a routine that creates the &start and &end variables. It looks at today's date and goes back one year.

How might I convert a date (03Mar2013) to a literal "20130301"? Once I have the literal I can do the comparison easily.

Re: Having a difficult time with a date conversion issue

Barnipaz — Thu, 06 Mar 2014 14:28:23 GMT

I miss something.

regis_dt and stop_dt are SAS date? If so they are numeric and their value is the number of day sinc january 1st 1960

You can check changing the format to best.

You should trasform the '20140301' to a date in this way using the input function

input('20140301',yymmdd8.)

So the datastep should be before the proc sql.

One of most difficult thing to understand in SAS is how it handles the date. It's very powerfull one you'll understand it

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 14:47:28 GMT

No, Barnipaz, regis_st is a literal ("20130301"). I need to convert &start (which is a sas date) to a literal in the same format.

Re: Having a difficult time with a date conversion issue

Linlin — Thu, 06 Mar 2014 15:00:37 GMT

example:

data have;

informat date1 date9.;

format date1 date9.;

input date1;

cards;

03Mar2013

02apr2013

05jan2013

;

data want;

set have;

date2=compress(put(intnx('month',date1,0,'b'),yymmdd10.),'-');

date3=compress(put(date1,yymmdd10.),'-');

proc print;run;

Obs date1 date2 date3

1 03MAR2013 20130301 20130303

2 02APR2013 20130401 20130402

3 05JAN2013 20130101 20130105

Re: Having a difficult time with a date conversion issue

Barnipaz — Thu, 06 Mar 2014 15:12:51 GMT

Yuo have start as numeric.

call symput('start',input("&st",mmddyy10.));

Right?

If so don't use the input function. It trasform char to alphanumeric.

It's hard to expain using a formum as a chat

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 15:23:57 GMT

DATA _NULL_;

call symput('start',input("&st",mmddyy10.));

call symput('end', input("&en",mmddyy10.));

call symput('date3', input("&st",yymmdd10.));

RUN;

When I do this code, I get the comparison is not the same data type error. Assume that date3 = 20130101, how do I make that a literal "20130101"?

Re: Having a difficult time with a date conversion issue

Reeza — Thu, 06 Mar 2014 15:35:56 GMT

What does &st and &en look like?

My assumption is that regis_dt and stop_dt is a character of the form 20130331 (YYYYMMDD).

Change your SQL to convert those to dates and you should be good to go.

Your data _null_ step is after your SQL, when it should be before.

proc sql;

CREATE table ccases1a

as SELECT

a.study AS study_num, i.inst_id, a.patient AS patient_id, input(a.regis_dt, yymmdd10.) as regis_dt format=date9., a.pt_sex AS sex_id, a.ethnicity AS ethnic_id

FROM alpha.rss_accrual as a

JOIN alpha.institution as i

ON a.inst_nci_id = i.nci_id

WHERE i.GROUP_ID = 1

AND input(a.regis_dt, yymmdd10.) >= &start. /*'20130301' */ If I use the '20130301' it works

AND (input(i.stop_dt, yymmdd10.) =. or input(a.regis_dt, yymmdd10.) <= &end.); /* '20140301'); */

quit;

Re: Having a difficult time with a date conversion issue

Linlin — Thu, 06 Mar 2014 15:36:23 GMT

I don't understand your question. if you have st=20130101 and want &date3=20130101 then try the code below:

%let st=20130101;

data _null_;

call symputx('date3',"&st");

run;

%put &date3;

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 15:51:37 GMT

When I put in this code:

proc sql;

CREATE table ccases1a

as SELECT

a.study AS study_num, i.inst_id, a.patient AS patient_id, a.regis_dt, a.pt_sex AS sex_id, a.ethnicity AS ethnic_id

FROM calgb.rss_accrual as a

JOIN calgb.institution as i

ON a.inst_nci_id = i.nci_id

WHERE i.GROUP_ID = 1

AND input(a.regis_dt, mmddyy10.) >= &start.

AND (input(i.stop_dt, mmddyy10.) =. or input(a.regis_dt, yymmdd10.) <= &end.);quit;

I also tried it using the format yymmdd10.

I get this error when running:

ERROR: INPUT function requires a character argument.

ERROR: Expression using equals (=) has components that are of different data types.

NOTE: The SAS System stopped processing this step because of errors.

I can't 'hardcode' the dates as it is a running 1 year tally.

Re: Having a difficult time with a date conversion issue

Reeza — Thu, 06 Mar 2014 16:35:43 GMT

So its a number, not a character date. Change the formulas as below in all the relevant places.

input(put(a.regis_dt, 8. -l), mmddyy10.)

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 16:56:40 GMT

Current code:

proc sql;

CREATE table ccases1a

as SELECT

a.study AS study_num, i.inst_id, a.patient AS patient_id, a.regis_dt, a.pt_sex AS sex_id, a.ethnicity AS ethnic_id

FROM calgb.rss_accrual as a

JOIN calgb.institution as i

ON a.inst_nci_id = i.nci_id

WHERE i.GROUP_ID = 1

and input(put(a.regis_dt, 8. -l), mmddyy10.) >= &start.

AND (input(put(i.stop_dt, 8. -l), mmddyy10.) =. or input(put(a.regis_dt, 8. -l), mmddyy10.) <= &end.);

quit;

current error:

ERROR: Numeric format F in PUT function requires a numeric argument.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0:00.01

cpu time 0:00.01

Re: Having a difficult time with a date conversion issue

Reeza — Thu, 06 Mar 2014 17:13:11 GMT

this means that stop_dt and regis_dt don't have the same formats. Clarify this. If it is a number use the one above, if its a character use the first one.

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 17:21:52 GMT

They are character fields. Here is what we have now.

24595 proc sql;

24596 CREATE table ccases1a

24597 as SELECT

24598 a.study AS study_num, i.inst_id, a.patient AS patient_id, a.regis_dt, a.pt_sex AS sex_id,

24598! a.ethnicity AS ethnic_id

24599 FROM calgb.rss_accrual as a

24600 JOIN calgb.institution as i

24601 ON a.inst_nci_id = i.nci_id

24602 WHERE i.GROUP_ID = 1

24606 AND input(a.regis_dt, yymmdd10.) >= &start.

24607 AND (input(i.stop_dt, yymmdd10.) =. or input(a.regis_dt, yymmdd10.) <= &end.);

ERROR: INPUT function requires a character argument.

ERROR: Expression using equals (=) has components that are of different data types.

24608 quit;

Is that the code you are referring to for character strings?

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 17:23:27 GMT

The second error had to do with the "=.". I changed that to = '' " and we're ok then.

Interestingly enough, this code works (in a data block).

where &start. <= input(regis_dt, yymmdd10.) <= &end.;

May the fact that it is contained in the sql code that is causing the problem.

Re: Having a difficult time with a date conversion issue

pasvorto — Thu, 06 Mar 2014 17:39:41 GMT

I got around it by doing this, which does not give me any errors:

proc sql;

CREATE table ccases1a

as SELECT

a.study AS study_num, i.stop_dt, i.inst_id, a.patient AS patient_id, a.regis_dt, a.pt_sex AS sex_id, a.ethnicity AS ethnic_id

FROM calgb.rss_accrual as a

JOIN calgb.institution as i

ON a.inst_nci_id = i.nci_id

WHERE i.GROUP_ID = 1 ;

quit;

data ccases1a; set ccases1a;

where input(regis_dt, yymmdd10.) >= &start.

AND (stop_dt =. or input(regis_dt, yymmdd10.) <= &end.);

run;

Re: Having a difficult time with a date conversion issue

Reeza — Thu, 06 Mar 2014 17:48:29 GMT

So basically, stop_dt is a valid SAS date and regis_dt is a character field already.

Glad you got it working.