Hello
I had added a couple policy numbers to my sas code and now I am getting this error below, it could be from the quotations as well but not entirely sure since I do have matching parenthesis.
(select distinct C.MBR_PGM_ID
,C.COV_EFF_DT ,C.COV_END_DT ,C.POL_NM ,C.POL_NBR ,C.lob_typ_id from STG_HSR.MBR_PGM_COV C where pol_nbr in
('X05951','005951','00005951', '000X05951','0X05951','000005951', 'X06163','006163','00006163','000X06163','0X06163','000006163',
'X00181','0X00181','000X00181','000181','00000181','000000181', '36000','036000','00036000','000036000', '360000', 'X06754',
'006754','00006754','000X06754','0X06754','000006754', 'X06764', '006764','00006764','000X06764','0X06764','000006764', 'X06029',
'006029','00006029','000X06029','0X06029','000006029', 'X07015', '007015','00007015','000X07015','0X07015','000007015', 'X08427',
'008427','00008427','000X08427','0X08427','000008427', 'X08401','008401','00008401','000X08401','0X08401','000008401',
'X08426','008426','00008426','000X08426','0X08426','000008426', 'X08434','008434','00008434','000X08434', '0X08434','000008434',
'X09211','009211','00009211','000X09211','0X09211','000009211', 'X09227','009227','00009227','000X09211','0X09227','000009227',
'X09228','009228','00009228','000X09228','0X09228','000009228', 'X09229','009229','00009229','000X09229','0X09229','000009229'
'X03024','003024','00003024','000X03024','0X03024','000003024' 'X05735','005735','00005735','000X05735','0X05735','000005735'
'X06029','006029','00006029','000X06029','0X06029','000006029' 'X09573','009573','00009573','000X09573','0X09573','000009573'
'X09574','009574','00009574','000X09574','0X09574','000009574') and C.COV_END_DT >= to_timestamp('01022018','mmddyyyy') and
C.COV_EFF_DT >= to_timestamp('01022008','mmddyyyy') ) as K ;
ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL statement: select distinct C.MBR_PGM_ID ,C.COV_EFF_DT
,C.COV_END_DT ,C.POL_NM ,C.POL_NBR ,C.lob_typ_id from STG_HSR.MBR_PGM_COV C where pol_nbr in ('X05951','005951','00005951',
'000X05951','0X05951','000005951', 'X06163','006163','00006163','000X06163','0X06163','000006163',
'X00181','0X00181','000X00181','000181','00000181','000000181', '36000','036000','00036000','000036000', '360000', 'X06754',
'006754','00006754','000X06754','0X06754','000006754', 'X06764', '006764','00006764','000X06764','0X06764','000006764',
'X06029', '006029','00006029','000X06029','0X06029','000006029', 'X07015',
'007015','00007015','000X07015','0X07015','000007015', 'X08427', '008427','00008427','000X08427','0X08427','000008427',
'X08401','008401','00008401','000X08401','0X08401','000008401',
'X08426','008426','00008426','000X08426','0X08426','000008426', 'X08434','008434','00008434','000X08434',
'0X08434','000008434', 'X09211','009211','00009211','000X09211','0X09211','000009211',
'X09227','009227','00009227','000X09211','0X09227','000009227',
'X09228','009228','00009228','000X09228','0X09228','000009228',
'X09229','009229','00009229','000X09229','0X09229','000009229'
'X03024','003024','00003024','000X03024','0X03024','000003024'
'X05735','005735','00005735','000X05735','0X05735','000005735'
'X06029','006029','00006029','000X06029','0X06029','000006029'
'X09573','009573','00009573','000X09573','0X09573','000009573'
My code
proc sql inobs=max outobs=max;
connect to oracle (USER="&OIMID." PASSWORD="&OIMPW." PATH='URNPRR');
create table input.mbr_pgmcv as
select distinct K.* from connection to oracle
(select distinct C.MBR_PGM_ID
,C.COV_EFF_DT
,C.COV_END_DT
,C.POL_NM
,C.POL_NBR
,C.lob_typ_id
/* added coverage effective and end date logic */
from STG_HSR.MBR_PGM_COV C
where pol_nbr in ('X05951','005951','00005951', '000X05951','0X05951','000005951',
'X06163','006163','00006163','000X06163','0X06163','000006163',
'X00181','0X00181','000X00181','000181','00000181','000000181',
'36000','036000','00036000','000036000', '360000',
'X06754', '006754','00006754','000X06754','0X06754','000006754',
'X06764', '006764','00006764','000X06764','0X06764','000006764',
'X06029', '006029','00006029','000X06029','0X06029','000006029',
'X07015', '007015','00007015','000X07015','0X07015','000007015',
'X08427', '008427','00008427','000X08427','0X08427','000008427',
'X08401','008401','00008401','000X08401','0X08401','000008401',
'X08426','008426','00008426','000X08426','0X08426','000008426',
'X08434','008434','00008434','000X08434', '0X08434','000008434',
'X09211','009211','00009211','000X09211','0X09211','000009211',
'X09227','009227','00009227','000X09211','0X09227','000009227',
'X09228','009228','00009228','000X09228','0X09228','000009228',
'X09229','009229','00009229','000X09229','0X09229','000009229'
'X03024','003024','00003024','000X03024','0X03024','000003024'
'X05735','005735','00005735','000X05735','0X05735','000005735'
'X06029','006029','00006029','000X06029','0X06029','000006029'
'X09573','009573','00009573','000X09573','0X09573','000009573'
'X09574','009574','00009574','000X09574','0X09574','000009574')
and C.COV_END_DT >= to_timestamp(&endt.,'mmddyyyy')
and C.COV_EFF_DT >= to_timestamp(&begdt.,'mmddyyyy')
) as K
;
disconnect from oracle;
) as K
;
disconnect from oracle;
as K is not needed
change select distinct K.* from connection to oracle
to select distinct * from connection to oracle
as K is needed for a derived table(not necessary in this scenario) .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.