proc sql doing a insert into within a call execute

[Madtim]

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

[Reeza]

Try removing the quotes and d.

[Madtim]

The quotes are not making the variable a character, as if I removed actually the sentence gets messy. Now, the d is to give date format when you insert dates, but removing it doesnt make a difference

[Astounding]

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;");

[RW9]

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.

[Patrick]

@Madtim 

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.