topic Re: LOOP ERROR - possibly data type reference error? in SAS Programming

LOOP ERROR - possibly data type reference error?

ptr_joo — Fri, 19 Apr 2019 08:29:45 GMT

libname mask xxxxxxxxxx;
%let pathway = xxxxxxxxxx;

%macro maxmin;
/* get variable names */
proc contents noprint data = &pathway out=contents
;run;

proc sql noprint;
create table contents_char as
select * from contents where type = 2
;quit;

proc sql noprint;
select count(*) into: cnt from contents_char
;quit;

%let cnt = &cnt;

proc sql noprint;
select name into: name1 - : name&cnt from contents_char
;quit;

/*get periods into an array*/

proc sql noprint;
select count(distinct reporting_period) into: cnt_p from &pathway
;quit;

proc sql noprint;
create table reporting_period_sorted as
select distinct reporting_period from &pathway order by reporting_period desc
;quit;

%let cnt_p = &cnt_p;

proc sql noprint;
select reporting_period into: period1 - : period&cnt_p from reporting_period_sorted
;quit;

/*get product types into an array*/

proc sql noprint;
select count(distinct product_ccar_type) into: cnt_pr from &pathway /*where product_ccar_type is not null*/
;quit;

proc sql noprint;
create table product_ccar_type as
select distinct product_ccar_type from &pathway /*where product_ccar_type is not null*/
;quit;

%let cnt_pr = &cnt_pr;

proc sql noprint;
select product_ccar_type into: product1 - : product&cnt_pr from product_ccar_type /*where product_ccar_type is not null*/
;quit;

/*delete results*/

proc delete data = results;run;

/*looping through the periods*/
%do j = 1 %to 1 /*&cnt_p*/;

%let period_len = 6;

/*looping through the products*/
%do k = 1 %to 1 /*&cnt_pr*/;

%let product_len = 5;

/* get length of all variable names and results */
%let name_len = 0;
%let count_len = 0;
%let countd_len = 0;
%let countnull_len = 0;

%do i = 1 %to &cnt;

proc sql noprint;
select count(&&name&i), count(distinct &&name&i), sum(case when &&name&i is null then 1 else 0 end)
into :count&i, :countd&i, :countnull&i
from &pathway
where reporting_period = put(&&period&j, 6.)

and product_ccar_type in ('CA_BC') - IT WORKS
and product_ccar_type = put(&&product&k, 10.) - IT GIVES ERROR:

_
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.

;quit;

%let count&i = %cmpres(&&count&i);
%let countd&i = %cmpres(&&countd&i);
%let countnull&i = %cmpres(&&countnull&i);

%if (&name_len < %length(&&name&i)) %then %let name_len = %length(&&name&i);
%if (&count_len < %length(&&count&i)) %then %let count_len = %length(&&count&i);
%if (&countd_len < %length(&&countd&i)) %then %let countd_len = %length(&&countd&i);
%if (&countnull_len < %length(&&countnull&i)) %then %let countnull_len = %length(&&countnull&i);
%end;

/*create results */
%do i = 1 %to &cnt;
data temp;
length PERIOD $&period_len PRODUCT $&product_len NAME $&name_len COUNT $&count_len COUNTD $&countd_len COUNTNULL $&countnull_len;
PERIOD = "&&period&j";
PRODUCT ="&&product&k";
NAME = "&&name&i";
COUNT = "&&count&i";
COUNTD = "&&countd&i";
COUNTNULL = "&&countnull&i";
run;
proc append base = results data= temp force
;run;
%end;

%end;

%mend maxmin;
%maxmin;

Re: LOOP ERROR - possibly data type reference error?

Kurt_Bremser — Fri, 19 Apr 2019 09:59:46 GMT

&&product&k

should resolve either to the name of a numeric column, or to a number.

Run your code again with

options mlogic mprint symbolgen;

to get all available information for macro debugging.

Re: LOOP ERROR - possibly data type reference error?

ptr_joo — Sun, 21 Apr 2019 11:16:04 GMT

Thanks for the answer. I have made the modifications suggested, however, I cannot find the solution yet. I am trying to describe the issue properly with attaching the outcome, maybe it helps :

1. I am constructing a loop by defining an array and taking its values from a specific column in the master table. (let's choose 'CA_BC' record as an example)

where reporting_period = put(&&period&j, 6.)
and product_ccar_type = &&product&k /* distinct values : CA_BC, CA_CC, CA_OC, CO_UI, RE_FR, OTH, RE_HE*/
;quit;

2. The macro stops with the following message - even if the loop takes 'CA_BC', it cannot find it, however, this value is taken from that very master table:

SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable K resolves to 1
SYMBOLGEN: Macro variable PRODUCT1 resolves to CA_BC
MPRINT(MAXMIN): select count(ACCOUNT_NUMBER), count(distinct ACCOUNT_NUMBER), sum(case when ACCOUNT_NUMBER is null then 1 else 0
end) into :count1, :countd1, :countnull1 from mask.mis_coe_au where reporting_period = put(201812, 6.) and product_ccar_type =
CA_BC ;
ERROR: The following columns were not found in the contributing tables: CA_BC.

3. If I change the code from product_ccar_type = &&product&k to product_ccar_type = ('CA_BC'), it works.

Re: LOOP ERROR - possibly data type reference error?

Astounding — Sun, 21 Apr 2019 12:08:46 GMT

Add double quotes around the expression:

and product_ccar_type = "&&product&k"

Re: LOOP ERROR - possibly data type reference error?

ptr_joo — Sun, 21 Apr 2019 12:21:34 GMT

Thank you very much for the solution! Amazing, it works.