Error with min and max date

LMSSAS — Fri, 04 Feb 2022 13:32:35 GMT

I am getting an error when I try and get the min and max date in my where clause. Can anyone make a suggestion? I pasted the error from the log beneth my code where a.gp_enrt_yrmo_nb ge (&minmonth.) and a.gp_enrt_yrmo_nb le (&maxmonth.) and a.pben_hlt_l5ky_nb <> 0

proc sql;
create table  EDW_Grp_Enrollment as
        select distinct
            a.gp_enrt_yrmo_nb,
            f.dacl_agey_stat_cd,
            f.sals_agey_gpcl_nm,
            a.dacl_sals_agey_cd,
            f.sals_agey_nm,
            a.dacl_sals_scag_cd,
            c.dacl_busg_lvl_1_de,
            c.dacl_busg_lvl_2_de,
            c.dacl_busg_lvl_3_de,
            c.dacl_busg_lvl_4_de,
            b.dacl_cnty_cd,
            a.sals_trt_cur_nb as rep_cd,
            b.gp_afln_id,
            b.dacl_gp_busg_cd,
            b.dacl_gp_bus_typ_cd,
            b.dacl_pri_crrr_cd,
            b.gp_nb,
			a.gp_divn_nb,
            b.gp_nm,
			a.dacl_subr_cnty_cd,
            b.gp_cnc_dT,
            b.gp_ognl_efcv_dt,
            b.gp_rnwl_dt,
			sum(mbr_bgni_ct) as beginning_inv_mbrs,
			sum(mbr_ei_ct) as ending_inv_mbrs,
            sum(a.arrm_ctc_bgni_ct) as beginning_inv_ctc,
            sum(a.arrm_ctc_nwsl_ct) as new_sale_ctc,
            sum(a.arrm_ctc_rnwl_ct)   as renewal_ctc,
            sum(a.arrm_ctc_cncn_ct) as cancel_ctc,
            sum(a.arrm_ctc_reen_ct) as reenrolled_ctc,
            sum(a.arrm_ctc_adtm_ct) as addsterms_ctc,
            sum(a.arrm_ctc_trf_ct)  as transfer_ctc,
            sum(a.arrm_ctc_ei_ct)  as ending_inv_ctc
	from edw.edw_stat_enrl a 
	   left join edw.edw_gp_dim b 
	  on  a.meta_gp_key_nb = b.meta_gp_key_nb 
	   left join edw.edw_busg_dim c
	 on  a.meta_busg_hstk_nb    = c.meta_busg_key_nb
	   left join edw.edw_sals_trty_activity_dim d
	  on a.sals_trt_hst_nb = d.sals_trt_nb
	   left join edw.edw_geographic_dim e
	  on a.meta_gp_fgok_nb = e.meta_geo_key_nb
	   left join  edw.edw_sals_agey_dim f 
	  on a.dacl_sals_agey_cd =f.dacl_sals_agey_cd  
	where a.gp_enrt_yrmo_nb ge (&minmonth.) and a.gp_enrt_yrmo_nb le (&maxmonth.) and a.pben_hlt_l5ky_nb <> 0
	and a.arrm_ctc_ei_ct   <> 0 and substrn(a.gp_nb,1,4) ne "9999"
	and b.gp_nb is not null and c.dacl_busg_lvl_3_de not in ('PRE MIPPA MED SUPP SELECT','PRE MIPPA MED SUPP STANDARD',
																'UNDER 65 QHP','TEMPORARY COVERAGE','MED SUPP STANDARD',
																'PRE MIPPA MED SUPP PRE STANDARD','BLUEMEDICARE NON-HMO',
																	'BLUEMEDICARE HMO','UNDER 65 NON-QHP',
																	'BLUEMEDICARE HMO BEHEALTHY','MED SUPP SELECT')
																	
																		 

	group by a.gp_divn_nb,
		a.gp_enrt_yrmo_nb ,
		a.dacl_subr_cnty_cd,
		f.dacl_agey_stat_cd,
		f.sals_agey_gpcl_nm ,
		a.dacl_sals_agey_cd  ,
		f.sals_agey_nm   ,
		a.dacl_sals_scag_cd ,
		c.dacl_busg_lvl_1_de ,
		c.dacl_busg_lvl_2_de ,
		c.dacl_busg_lvl_3_de ,
		c.dacl_busg_lvl_4_de ,
		b.dacl_cnty_cd,
		a.sals_trt_cur_nb,
		b.gp_afln_id,
		b.dacl_gp_busg_cd,
		b.dacl_gp_bus_typ_cd,
		b.dacl_pri_crrr_cd,
		b.gp_nb,
		b.gp_nm,
		b.gp_cnc_dt,
		b.gp_ognl_efcv_dt,
		b.gp_rnwl_dt 		
	order by
		a.gp_enrt_yrmo_nb, 
		b.gp_nb;
quit;

82         	where a.gp_enrt_yrmo_nb ge (&minmonth.) and a.gp_enrt_yrmo_nb le (&maxmonth.) and a.pben_hlt_l5ky_nb <> 0
                                                  _
                                                  22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.  

ERROR 22-322: Syntax error, expecting one of the following: a name, *.  

82         	where a.gp_enrt_yrmo_nb ge (&minmonth.) and a.gp_enrt_yrmo_nb le (&maxmonth.) and a.pben_hlt_l5ky_nb <> 0
                                                                              _
                                                                              22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.  

WARNING: Apparent symbolic reference MAXMONTH not resolved.
82         	where a.gp_enrt_yrmo_nb ge (&minmonth.) and a.gp_enrt_yrmo_nb le (&maxmonth.) and a.pben_hlt_l5ky_nb <> 0
                                                                                        _
                                                                                        22
ERROR 22-322: Syntax error, expecting one of the following: a name, *.

Re: Error with min and max date

PaigeMiller — Fri, 04 Feb 2022 13:37:39 GMT

What are the values of macro variables &minmonth and &maxmonth when you run this?

Perhaps you need to turn on the macro debugging option SYMBOLGEN before you run this, run it again and check the log.

options symbolgen;

Re: Error with min and max date

LMSSAS — Fri, 04 Feb 2022 14:19:05 GMT

Thanks Paige,
I actually discovered that I didn't set the macro at the begining of the query.
%let minmonth = 202112;
%let maxmonth = 202112;

Thank Again!!

topic Error with min and max date in SAS Programming

Error with min and max date

Re: Error with min and max date

Re: Error with min and max date