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?
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.
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.
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.
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.
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;
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.
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
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.
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
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"?
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;
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;
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
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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: