topic Connection to ODBC in New SAS User

Connection to ODBC

Michelle_ — Mon, 17 Jul 2023 06:12:05 GMT

Hi all, seeking help on the coding below.

I would like to set %let statement to read the period equal to 2 months after begin date. (i.e. 202304)

If i direct set period_YM = 202304, the program is works. However, instead of direct set the period_YM, i would like to build a %let statement to calculate in future.

But, the error pop up as below. Would like to seek any solution to solve this issue?

ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis : [Oracle][ODBC]Invalid descriptor index <1>.

%let begin = 202302;

%let period 	= "%sysfunc(intnx(month,%sysfunc(inputN(&begin.01,yymmdd10.)),+2),date9.)"d;
%let period_YM 	= input(substr(put(&period., yymmddn8.),1, 6), 6.);

Proc sql;
CONNECT TO ODBC (DATAsrc=KPODA01 USER=&OD_USER_ID PASSWORD=&OD_PASSWORD);
CREATE TABLE working.output as select * from connection to ODBC
(select distinct 
    Column 1,
    Column 2
from oracle.File_Name_&period_YM.);

Thanks in advance!

Re: Connection to ODBC

JosvanderVelden — Mon, 17 Jul 2023 08:29:57 GMT

If you place the code below after the last %let and verify the log you will see why it is not working as you want.

%put begin = &begin.;
%put period = &period.;
%put period_YM = &period_YM.;

I suggest to use for the %let period_YM statement something like:

%let period_YM 	= %sysfunc(inputn(%sysfunc(substr(%sysfunc(putn(&period., yymmddn8.)),1, 6)), 6.));

Re: Connection to ODBC

ballardw — Mon, 17 Jul 2023 08:30:41 GMT

If you want a value of 202304 why did you 1) apply a date9 format, include quotes and the d character?

One suggestion:

%let begin = 202302;

%let period 	= %sysfunc(intnx(month,%sysfunc(inputN(&begin,yymmn6.)),+2),yymmddn8.);

Note use of the YYMMN6 informat to imply the day of the month to be one.

If you are using the date value of period elsewhere such as for comparison create a separate variable and do not format it all.

@Michelle_ wrote:

Hi all, seeking help on the coding below.

I would like to set %let statement to read the period equal to 2 months after begin date. (i.e. 202304)

If i direct set period_YM = 202304, the program is works. However, instead of direct set the period_YM, i would like to build a %let statement to calculate in future.

But, the error pop up as below. Would like to seek any solution to solve this issue?
ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis : [Oracle][ODBC]Invalid descriptor index <1>.
%let begin = 202302;

%let period 	= "%sysfunc(intnx(month,%sysfunc(inputN(&begin.01,yymmdd10.)),+2),date9.)"d;
%let period_YM 	= input(substr(put(&period., yymmddn8.),1, 6), 6.);

Proc sql;
CONNECT TO ODBC (DATAsrc=KPODA01 USER=&OD_USER_ID PASSWORD=&OD_PASSWORD);
CREATE TABLE working.output as select * from connection to ODBC
(select distinct 
    Column 1,
    Column 2
from oracle.File_Name_&period_YM.);
Thanks in advance!

Re: Connection to ODBC

Michelle_ — Mon, 17 Jul 2023 13:12:16 GMT

It works. I have to add the %sysfunc in the &period_YM too. Thanks!