BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
suncawy
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

5 REPLIES 5
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
FreelanceReinh
Jade | Level 19

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

suncawy
Obsidian | Level 7

 

 

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

 

FreelanceReinh
Jade | Level 19

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.

suncawy
Obsidian | Level 7

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

Thank you.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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