Issue when trying to achieve a dynamic SQL Where statement with a Macro Variable that contains symbo

[StickyRoll]

Issue when trying to achieve a dynamic SQL Where statement with a Macro Variable that contains symbol

I have a script to perform PROC SQL Update in which the Where statement (both left and right side of the Where statement) is dynamic.

However, I am getting this error:

ERROR: Function STRIP requires a character expression as argument 1.

The script as below.

%let in_dataset_aoc = land.pvcf;
%let out_dataset_aoc = work.pvcf2;

%let concat_str_aoc = strip(ENTITY_ID)||'#'||strip(REPORTING_DT)||'#'||strip(INSURANCE_CONTRACT_GROUP_ID)||'#'||strip(CASHFLOW_TYPE_CD)||'#'||strip(CURRENCY_CD);

data load_process;
	set &in_dataset_aoc.;
	format KEY $10000.;
	KEY = &concat_full_aoc.;
run;

proc sql noprint;
	select key, PV_RSK_ADJ_AMT, PV_BEL_AMT, PV_BV_BEL_AMT, PV_LIC_BEL_CUR_AMT, PV_LIC_BEL_PT_AMT, PV_LIC_RA_CUR_AMT, PV_LIC_RA_PT_AMT into :key_1-, :pv_rsk_adj_amt_1-, :pv_bel_amt_1-, :pv_bv_bel_amt_1-, :pv_lic_bel_cur_amt_1-, :pv_lic_bel_pt_amt_1-, :pv_lic_ra_cur_amt_1-, :pv_lic_ra_pt_amt_1-
	from load_process;
	
	select count(1) into :update_cnt
	from load_process;
quit;

%macro update_pvcf;
	%if &update_cnt. >= 1 %then %do;
		%do i = 1 %to &update_cnt.;
			proc sql; 
				update &out_dataset_aoc.
				set PV_RSK_ADJ_AMT = 1
				where &concat_full_aoc. = "&&key_&i..";
			quit;
		%end;
	%end;
%mend;

%update_pvcf;

What could be wrong here? the Macro Variable couldn't resolve in the Where statement? I tried quote and unquote but it still won't work.

H

[Kurt_Bremser]

Rule #1 of macro development: start with working non-macro code.

So show us your code that works for a single instance; if you did not start with that, then that's the reason.

[LinusH]

I don't see the declaration of concat_full_aoc.

[PaigeMiller]

However, I am getting this error:

ERROR: Function STRIP requires a character expression as argument 1.

This is not a macro error. This is a base SAS error where your STRIP function isn't working.

Agreeing with @Kurt_Bremser , first you need to get the code to work without macro variables on one instance. You have not done that. Once you get it to work, you will find it much easier to get it to work with macro variables. And if you don't have working SAS code, then it will never work with macros or macro variables. This is an extremely important point, but most people ignore this advice ... don't ignore this advice. Following this advice will not only help with this specific problem, but many many many problems in the future.