topic Re: Macro Error in SAS Programming

Macro Error

suncawy — Thu, 17 Dec 2015 16:11:20 GMT

Hi,

I'm not sure why I get a syntax error. It says I'm missing an extra paren. This is extracting data from an Oracle database, however, this same exact macro works in teradata. This is the macro code, I'm using the %bquoute to help avoid an error about more positional parameters than defined.

%macro looper(_tblname, _varnme);
*-----------------------------------------------------*
IDENTIFY CLAIMS/ENCOUNTERS (XCELYS PROFESSIONAL) BY
ICD9 DIAGNOSIS CODES
*-----------------------------------------------------*;
Proc SQL;
/*CONNECT to ORACLE (path= 'ISARNP.world' );*/
Create Table &_tblname as
/* VARIABLE NAMES USED IN HEDIS PROGRAMMING: */
(Select Distinct /* ----------------------------------------- */
A.clm_nb /* CLM_NBR */
,A.clm_sts_cd
,A.src_sys_cd
,A.frst_svc_dt /* SVC_B_DT */
,A.prmy_diag_cd
,D.indvd_id_nb /* INDV_HRN */
,E.plc_of_svc_cd /* POS_CD */
,B.diag_lne_nb
,B.icd_diag_cd /* DIAG_CD */
From
CDW.PROF_CLM_HDR A
/*INNER JOIN
CDW.prof_CLM_HDR_PRVDR F
on A.prof_clm_ik = F.prof_clm_ik
and A.rgn_cd = F.rgn_cd
and A.src_sys_cd = F.src_sys_cd*/
INNER JOIN
CDW.PROF_CLM_DTL C
on A.prof_clm_ik = C.prof_clm_ik
and A.rgn_cd = C.rgn_cd
and A.src_sys_cd = C.src_sys_cd
INNER JOIN
CDW.PROF_CLM_INDVD D
on A.prof_clm_ik = D.prof_clm_ik
and A.rgn_cd = D.rgn_cd
and A.src_sys_cd = D.src_sys_cd
INNER JOIN
CDW.PROF_CLM_PLC_OF_SVC E
on A.prof_clm_ik = E.prof_clm_ik
and A.rgn_cd = E.rgn_cd
and A.src_sys_cd = E.src_sys_cd
INNER JOIN
CDW.PROF_CLM_HDR_DIAG B
on A.prof_clm_ik = B.prof_clm_ik
and A.rgn_cd = B.rgn_cd
and A.src_sys_cd = B.src_sys_cd
Where
A.src_sys_cd = 'Xcelys ROC'
and A.rgn_cd ='CO'
and A.frst_svc_dt Between &BEGDATE AND &ENDDATE
and A.most_rcnt_clm_ver_in_cd='Y'
and D.indvd_role_cd = 'Patient'
/*and F.prvdr_role_cd = 'Rendering - Billing'*/
and A.intrl_extl_cd = 'External'
/*-- TO FILTER BY DIAGNOSIS --*/
and B.clsfn_schm_cd in ('ICD-9','ICD-10')
and B.icd_diag_cd in %str(&&_varnme.)
);
Quit;

%mend;
%looper(CHL_PROF_DIAG1, %bquote(&TBL_B_DIAG_SEXW5A_CDWX.));

Re: Macro Error

Quentin — Thu, 17 Dec 2015 16:50:55 GMT

Hi, I don't have an anwer, but some thoughts.

If the exact macro works to pull data from Teradata, but not Oracle, you should look a the parts of the macro that deped on the source (e.g. the connection string), to see what differs.
When you say you're getting a syntax error, what error do you get, and when do you get it? For example, do you get it when the macro compiles, or when it executes? Does it look like an eror message from SAS or from Oracle?
As written it's a lot of code for people to review. It's a good candidate for a bit of brute force debugging on your end. If you cut the query in half, do you get an error? Then repeat cutting in half until you get to the point where error magically goes away, or you have a simple query (PROC SQL step with 5-10 lines) which still shows an error, and post that.
Also curious what &TBL_B_DIAG_SEXW5A_CDWX resolves to, any other global macro vars referenced like &BEGDATE and &ENDDATE.

Hope that helps a start.

Re: Macro Error

FreelanceReinh — Thu, 17 Dec 2015 17:24:14 GMT

I think you overdid the macro quoting. Using %BQUOTE, %STR and a double ampersand together looks suspicious to me. Indeed, it would be interesting what type of contents &TBL_B_DIAG_SEXW5A_CDWX. resolves to.

Please note that the IN operator in the line and B.icd_diag_cd in %str(&&_varnme.) at the end of your macro requires a list in parentheses. Probably, these parentheses are not contained in &TBL_B_DIAG_SEXW5A_CDWX. (I guess that's why you use %BQUOTE). But %STR(&&_varnme.) won't create them either. So, it is not surprising that you may be "missing an extra paren." (in fact two of them).

On the other hand, if you could include the parentheses into &TBL_B_DIAG_SEXW5A_CDWX., so that this macro variable resolves to, say, ('R94.120', 'H61.113'), i.e., a list of ICD codes in parentheses, I think you could simply say (in the abovementioned line of code)

and B.icd_diag_cd in &_varnme.

and call the macro as simply as

%looper(CHL_PROF_DIAG1, &TBL_B_DIAG_SEXW5A_CDWX.);

Re: Macro Error

suncawy — Thu, 17 Dec 2015 17:50:51 GMT

Thank you for the help. The &TBL_B_DIAG_SEXW5A_CDWX does resolve to a list of codes because of restrictions, I can only have a list of 1,000 codes to pass to a macro variable, so there is a macro ..SEXW5B, SEXW5C, etc. and each macro variable contains less than the 1,000 of those. This is an example of the issue:

NOTE: Line generated by the macro variable "_VARNME".
1 '630','631.0','631.8','632','633.00','633.01','633.10','633.11','633.20','633.21','633.80','633.81','633.90','633.91','634.00'
-
22
-
76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, (, -, SELECT.

"at the end of your macro requires a list in parentheses. Probably, these parentheses are not contained in &TBL_B_DIAG_SEXW5A_CDWX. (I guess that's why you use %BQUOTE). But %STR(&&_varnme.) won't create them either." Yes, you are absolutely correct about this, so the problem is I'm not sure how else to include the parentheses in the macro variable.

If I don't do %BQUOTE, then the error becomes "more positional parameters than defined".

Re: Macro Error

FreelanceReinh — Thu, 17 Dec 2015 17:57:35 GMT

You could, for example, insert the parentheses in the macro call:

%looper(CHL_PROF_DIAG1, (&TBL_B_DIAG_SEXW5A_CDWX.));

and still use the simple

and B.icd_diag_cd in &_varnme.

Re: Macro Error

suncawy — Thu, 17 Dec 2015 18:15:48 GMT

Perfect ! it worked..Thank you very much. For me lesson learned, make it less complex and keep it simple.

Thank you.