I am using call execute to pass values from a table to parameters in my macro code. The intent is to produce an output table for each prot and table containing only specific fields based on my projects meta data. One of the parameters is a list of fields that I have concatenated together that I want to use in a keep statement, e.g.
prot | table | char_fields | num_fields |
---|---|---|---|
AAA01 | 01 | CHAR1 CHAR2 CHAR3 CHAR4 | NUM1 NUM2 NUM3 NUM4 |
AAA01 | 02 | CHAR1 CHAR2 CHAR3 CHAR4 | NUM1 NUM2 NUM3 NUM4 |
BBB01 | 01 | CHAR1 CHAR2 CHAR3 CHAR4 | NUM1 NUM2 NUM3 NUM4 |
BBB01 | 02 | CHAR1 CHAR2 CHAR3 CHAR4 | NUM1 NUM2 NUM3 NUM4 |
One variable in the source table is a list of character formatted fields, the other is a list of numeric formatted fields. I am passing the char_fields value to a character array within the macro, and the num_fields to a numeric array in the macro.
%macro freetext (prot,table,charfield,numfield);
data &prot._&table1
set &prot._&table;
by prot;
array charfield (*) &charfields:;
array numfield (*) &numfields:;
keep site prot patid &charfields &numfields; run;
%mend freetext
data _null_;
set source;
call
execute('%freetext('||prot||','||table||','||charfields||','||numfields||')');
run;
Now, this works for 99% of the tables produced via the macro, but for a few, the macro seems to be mis-interpreting the field type in the array statements and throws this error:
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
I have tried executing the data step outside of the macro by hard coding the array and I do not get this error message. Does anyone happen to know why the array is behaving differently within the macro vs. outside of it?
The specification of the array variable list with the added colon : is probably not doing what you think and or what you want.
array charfield (*) &charfieldlist:;
If CHARFIELDLIST is CHAR1 CHAR3 then you are asking for variables CHAR1 and all variable that begin with CHAR3. I think if you remove that colon your problem will be solved.
Hi,
Can you provide test data in a datastep which causes the issue, and the exact code you are using. The code you posted is wrong in several ways:
%macro freetext (prot,table,charfield,numfield);
data &prot._&table1 /* Missing semicolon to close data, and missing dot to finish macro variable */
set &prot._&table; / * we have no example of what AAA01.01 looks like, and datasets should not be numeric start */
by prot;
array charfield (*) &charfields:; /* Macro variables are terminated by a full stop, not a semicolon. Charfields is not a macro variable witihin this code &CHARFIELD. */
array numfield (*) &numfields:; /* As above */
keep site prot patid &charfields &numfields; run; /* as above.
%mend freetext
data _null_;
set source;
call
execute('%freetext('||prot||','||table||','||charfields||','||numfields||')'); /* Missing semicolon after first ) */
run;
Sorry, in my haste to post this and anonymize my code there were probably some typos. Here is my complete code:
%macro freetext (prot,table,freefieldlist,charfieldlist,numfieldlist);
data &prot._&table;
set &prot..&table;
by prot;
array charfield (*) &charfieldlist:;
array numfield (*) &numfieldlist:;
keep site prot patid &charfieldlist &numfieldlist; run;
%mend freetext;
data _null_;
set source;
call
execute('%freetext('||prot||','||table_name||','||freefieldlist||','||charfieldlist||','||numfieldlist||')');
run;
Here is an example of the source table I am using in call execute:
prot | table_name | charfieldlist | numfieldlist |
---|---|---|---|
ABC01 | TAB1 | CHAR1 CHAR3 CHAR5 | NUM1 NUM3 NUM4 |
ABC02 | TAB2 | CHAR6 CHAR7 CHAR | NUM6 NUM7 NUM8 |
ABC03 | TAB3 | CHAR11 CHAR13 CHAR14 | NUM12 NUM13 NUM14 |
Here is an example of &prot..&table used in set statement (in this case the TAB1 table for prot ABC01):
site | prot | patid | CHAR1 | CHAR2 | CHAR3 | CHAR4 | CHAR5 | NUM1 | NUM2 | NUM3 | NUM4 | NUM5 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
Site1 | ABC01 | 001 | ABC | DEF | GHI | JKL | MNO | 111 | 222 | 333 | 444 | 555 |
Site2 | ABC01 | 002 | AAB | DDE | GGH | JJK | MMN | 112 | 223 | 334 | 445 | 556 |
Site3 | ABC01 | 003 | BBC | EEF | HHI | KKL | NNO | 113 | 224 | 335 | 446 | 557 |
Here is an example of the &prot._&table output after the macro executes:
site | prot | patid | char1 | char3 | char5 | num1 | num3 | num4 |
---|---|---|---|---|---|---|---|---|
Site1 | ABC01 | 001 | ABC | GHI | MNO | 111 | 333 | 444 |
Site2 | ABC01 | 002 | AAB | GGH | MMN | 112 | 334 | 445 |
Site3 | ABC01 | 003 | BBC | HHI | KKL | 113 | 335 | 446 |
The specification of the array variable list with the added colon : is probably not doing what you think and or what you want.
array charfield (*) &charfieldlist:;
If CHARFIELDLIST is CHAR1 CHAR3 then you are asking for variables CHAR1 and all variable that begin with CHAR3. I think if you remove that colon your problem will be solved.
This solved the issue, I probably copied this from another line somewhere and accidentally retained the colon.
array charfield (*) &charfields:;
should be
array charfield (*) $ &charfields;
... and, for a macro which calls a macro,
this replaces your call execute step;
see
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.