Having a difficult time with a date conversion issue

[pasvorto]

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?

[Reeza]

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.

[StudyAnalytics_in]

Hai Reeza,

I had to work with a clients data where it looks like

10102 - ymmdd

10227 - ymmdd

20020308 - yyyymmdd

there is a colon midifier if i read directly, but not sure will this can be handled by the format yymmdd10.  plz clarify.

and the data contains different lengths as vintages.

[Reeza]

Usually use anydtdte. for that type of data.

If you claim to be teaching SAS, I'd assume you'd be able to verify this as well.

[Patrick]

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.

[Reeza]

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;

[pasvorto]

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.

[Barnipaz]

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

[pasvorto]

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.

[Linlin]

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

[pasvorto]

  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"?

[Reeza]

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;

[Linlin]

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;

[Barnipaz]

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

[pasvorto]

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.