Within a data setp I am using a "call execute" like the one below:
call execute("proc sql;
insert into leaddata values("||subjid||","||mhterm||","||mhstdtc_dtr||","||mhstdtc_dtr||","||mhstdtc_dtr||","||sitemnemonic||","||mhstdtc2||"d,"||mhendtc2||"d);quit;");
Everything works fine except the last 2 variables mhstdtc2 and mhendtc2, these variables are num type with an informat and format of date9.
I receive a note that numeric variables are being converted to character on that line, How should the dates be inserted on the call execute statment. Thanks
If you examine the end of the CALL EXECUTE logic, you are generating the wrong syntax:
","||mhstdtc2||"d,"||mhendtc2||"d);quit;");
Assuming your date variables actually contain properly formatted date values, you are still generating something like:
, 11Feb2019d, 12feb2019d); quit;
To give this the proper format, you need to generate date literals that look like this instead:
, '11Feb2019'd, '12Feb2019'd); quit;
This ought to do the trick:
", '"||mhstdtc2||"'d, '"||mhendtc2||"'d);quit;");
Presumably the date variables contain the right number of characters, but if they give you any trouble you could apply the STRiP function to each: || strip(mhstdtc2) || and || strip(mhendtc2) ||
************************
On second thought, it gets a little more complicated. If your variables are numeric, you would need:
", '"||put(mhstdtc2, date9.)||"'d, '"||put(mhendtc2, date9.)||"'d);quit;");
Well, for an easy fix:
str=catx(',',subjid,mhterm,mhstdtc_dtr,mhstdtc_dtr,mhstdtc_dtr,
sitemnemonic,put(mhstdtc2,best.),put(mhenddtc2,best.));
call execute(cats("proc sql; insert into leaddata values(",str,"); quit;'));
Note how I do the concatenation first, its easier to read. You should just be able to put() the numbers into a string and have that inserted correctly, dates (and times/datetimes) are just numbers which are then "formatted" to look like dates.
I would however question why you are doing this at all, it really can't be the best method to get what you want.
I'm very much with @RW9 to take an approach where you first populate a SAS variable with the full command then then just use a call execute(<variable>);
Taking such an approach lets you "put" the variable into the log and you can see the full generated command and syntax check it. You can even copy/past it from the log to another program window and execute it. This makes debugging much easier - at least for me.
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.