topic Re: proc sql doing a insert into within a call execute in SAS Programming

proc sql doing a insert into within a call execute

Madtim — Tue, 12 Feb 2019 02:38:38 GMT

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

Re: proc sql doing a insert into within a call execute

Reeza — Tue, 12 Feb 2019 02:50:44 GMT

Try removing the quotes and d.

Re: proc sql doing a insert into within a call execute

Madtim — Tue, 12 Feb 2019 02:53:28 GMT

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

Re: proc sql doing a insert into within a call execute

Astounding — Tue, 12 Feb 2019 03:46:48 GMT

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

Re: proc sql doing a insert into within a call execute

RW9 — Tue, 12 Feb 2019 09:50:55 GMT

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.

Re: proc sql doing a insert into within a call execute

Patrick — Tue, 12 Feb 2019 12:36:00 GMT

@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.