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

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, *.

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
LMSSAS
Quartz | Level 8
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!!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 614 views
  • 1 like
  • 2 in conversation