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

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;

%end;

%mend maxmin;
%maxmin;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star
Add double quotes around the expression:

and product_ccar_type = "&&product&k"

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User
&&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.

ptr_joo
Calcite | Level 5

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.

 

 

Astounding
PROC Star
Add double quotes around the expression:

and product_ccar_type = "&&product&k"
ptr_joo
Calcite | Level 5
Thank you very much for the solution! Amazing, it works.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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
  • 4 replies
  • 1076 views
  • 0 likes
  • 3 in conversation