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.));
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.
Hi, I don't have an anwer, but some thoughts.
Hope that helps a start.
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.);
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".
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.
Perfect ! it worked..Thank you very much. For me lesson learned, make it less complex and keep it simple.
Thank you.
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.