BookmarkSubscribeRSS Feed
pasvorto
Calcite | Level 5

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?

23 REPLIES 23
Reeza
Super User

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
Calcite | Level 5

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
Super User

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
Opal | Level 21

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
Super User

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
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

  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
Super User

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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 Smiley Happy

pasvorto
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 23 replies
  • 3571 views
  • 0 likes
  • 7 in conversation