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) .
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.