22-322: Syntax error

[Rsadiq]

Hello

I cant seem to figure out my error in the tables I am creating, it says expecting as, like or (

Proc sql inobs=max outobs=max;
 Create table work.mbr_program;
 select distinct L.mbr_id,
 L.ENRL_DT,
 L.MBR_PGM_STS_TYP_ID,
 L.MBR_PGM_STS_RSN_TYP_ID
 From input.mbr_pgm L,
 STG_HSR.MBR_PGM P
 where L.ENRL_DT is not null
            and P.MBR_PGM_STS_RSN_TYP_ID ='81'
             and P.MBR_PGM_STS_TYP_ID ='05';
             quit;
            
     Proc sql inobs=max outobs=max;
        Create table work.mbr_program_wellness;
        select distinct a.*,
        L.mbr_pgm_id  as MP_MBR_PGM_ID,
        L.ENRL_DT, 
        L.END_DT,
        L.MBR_PGM_STS_TYP_ID,
        L.MBR_PGM_STS_RSN_TYP_ID
        From sasdata.five a,
             input.mbr_pgm L
        where L.mbr_id = a.mbr_id
            and L.END_DT >= (today() - 30)
            and L.MBR_PGM_STS_RSN_TYP_ID ='81'
             and L.MBR_PGM_STS_TYP_ID ='05'
             and a.AYB_MODALITY = '2_coach';
             quit;       

Proc sql inobs=max outobs=max;
373                Create table work.mbr_program_wellness;
                                                         _
                                                         22
                                                         76
ERROR 22-322: Syntax error, expecting one of the following: (, AS, LIKE. 

ERROR 76-322: Syntax error, statement will be ignored.

[Kurt_Bremser · Accepted solution]

The whole create table statement must not be broken by a semicolon; Everything (create table, select from, where etc) needs to be one statement:

proc sql inobs=max outobs=max;
create table work.mbr_program_wellness as
select
  distinct a.*,
  L.mbr_pgm_id  as MP_MBR_PGM_ID,
  L.ENRL_DT, 
  L.END_DT,
  L.MBR_PGM_STS_TYP_ID,
  L.MBR_PGM_STS_RSN_TYP_ID
from
  sasdata.five a,
  input.mbr_pgm L
where
  L.mbr_id = a.mbr_id
  and L.END_DT >= (today() - 30)
  and L.MBR_PGM_STS_RSN_TYP_ID ='81'
  and L.MBR_PGM_STS_TYP_ID ='05'
  and a.AYB_MODALITY = '2_coach'
;
quit;

Do the same in your first SQL.

[Rsadiq]

Okay thank you I will try it out!

[Kurt_Bremser]

Actually, this (in red)

ERROR 22-322: Syntax error, expecting one of the following: (, AS, LIKE.

is the significant part of the ERROR message. The keyword "as" is what connects the create table with the rest.

[moorsd]

What happens if you add an 'AS' on the FROM statement. See below:

Do you still get the ERROR?

proc sql inobs=max outobs=max;
create table work.mbr_program_wellness as
select
  distinct a.*,
  L.mbr_pgm_id  as MP_MBR_PGM_ID,
  L.ENRL_DT, 
  L.END_DT,
  L.MBR_PGM_STS_TYP_ID,
  L.MBR_PGM_STS_RSN_TYP_ID
from
  sasdata.five AS a,
  input.mbr_pgm AS L
where
  L.mbr_id = a.mbr_id
  and L.END_DT >= (today() - 30)
  and L.MBR_PGM_STS_RSN_TYP_ID ='81'
  and L.MBR_PGM_STS_TYP_ID ='05'
  and a.AYB_MODALITY = '2_coach'
;
quit;