DATA Step, Macro, Functions and more

Macro Error

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Macro Error

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


Accepted Solutions
Solution
‎12-17-2015 01:58 PM
Trusted Advisor
Posts: 1,117

Re: Macro Error

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.

View solution in original post


All Replies
PROC Star
Posts: 1,322

Re: Macro Error

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

  1. 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.
  2. 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?
  3. 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.
  4. 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.

Trusted Advisor
Posts: 1,117

Re: Macro Error

[ Edited ]

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

Contributor
Posts: 24

Re: Macro Error

Posted in reply to FreelanceReinhard

 

 

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

 

Solution
‎12-17-2015 01:58 PM
Trusted Advisor
Posts: 1,117

Re: Macro Error

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.

Contributor
Posts: 24

Re: Macro Error

Posted in reply to FreelanceReinhard

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

Thank you.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 234 views
  • 0 likes
  • 3 in conversation