Macro ID list?

[ybz12003]

Hi experts:

I created several macro ID lists from different test datasets, such as test 1, test 2, test 3 and test 4.  I would like to do the following, however, I found error message from my lot window.  Any idea why and how to fix it?

proc sql noprint;
	select quote(strip(id)) into : test1_list separated by ',' from test1;
quit;
 
%put &test1_list;
.
.
proc sql noprint;
	select quote(strip(id)) into : test4_list separated by ',' from test4;
quit;
 
%put &test4_list;

data want;
	set have;
	if caseid in (&test1_list., &test2_list., &test3_list.,  &test4_list.) then output want;
	if caseid in (&test1_list) then Mark = 1;
	if caseid in (&test2_list) then Mark = 2;
	if caseid in (&test3_list) then Mark = 3;
	if caseid in (&test4_list) then Mark = 4;
run;

92 data have;
93 set want;
94 if caseid in (&test1_list., &test2_list.,
94 ! &test3_list., &test4_list.) then output have;
       -
      22
WARNING: Apparent symbolic reference test3_LIST not resolved.
94 &test3_list., &test4_list.) then output
     -
    200
94 ! want;
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
a datetime constant, a missing value, iterator, (.

ERROR 200-322: The symbol is not recognized and will be ignored.

95 if caseid in (&test1_list) then Mark = 1;
96 if caseid in (&test2_list) then Mark = 2;
97 if caseid in (&test3_list) then Mark = 3;
                         -
                       22
                       76
WARNING: Apparent symbolic reference test3_LIST not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
a datetime constant, a missing value, iterator, (.

ERROR 76-322: Syntax error, statement will be ignored.

98 if caseid in (&test4_list) then Mark = 4;
99 run;

NOTE: The SAS System stopped processing this step because of errors.

[PaigeMiller]

94 ! &test3_list., &test4_list.) then output have;
       -
      22
WARNING: Apparent symbolic reference test3_LIST not resolved.
94 &test3_list., &test4_list.) then output

Obviously, Test3_List is not resolved, and that happens because Test3_List does not exist; but you need to show us the SAS Log of the entire program, not just the few lines of code where the error is. Furthermore, you need to paste the entire text of your SAS log into the window that appears when you click on the {i} icon DO NOT SKIP THIS STEP.

[noling]

Does &test3_LIST have a value? Can you print out a sample of the values for all 4 of your test<#>_LIST's?

[Tom]

If no observations are returned by the query then the SQL INTO clause does not create the macro variable.

Set the macro variable to a default value before the query.

%let test3_list=;
select ... into :test3_list separated by ' ' ....;

And then later on make sure you don't end up with commas with nothing between them.  Since SAS doesn't need the commas between the values used in the list of values passed to the IN operator just don't include them. Use spaces as the delimiter instead. Multiple adjacent spaces do not cause any trouble.

if caseid in (&test1_list.  &test2_list. &test3_list.  &test4_list.) then output want;

[ybz12003]

Below is the examples of test3 ID list after creating a macro.

"PR8G00225","KW1H55007"

[PaigeMiller]

---

@ybz12003 wrote:

Below is the examples of test3 ID list after creating a macro.

"PR8G00225","KW1H55007"

---

This doesn't help. When a user says the macro variable exists, and SAS says that it does not exist (cannot be resolved), then I believe SAS. Therefore ...

We need to see the entire SAS log, pasted into the window that appears by clicking on the {i} icon.

[RW9]

Still refusing to use plain code then?

data code_lists;
  set test1 (in=a)
      test2 (in=b)
      test3 (in=c)
      test4 (in=d);
  if a then mark=1;
  if b then mark=2;
  if c then mark=3;
  if d then mark=4;
run;

data want;
  merge have (in=a)
        code_lists (in=b rename=(id=caseid));
  by caseid;
  if a and b;
run;

Sorting the first dataset out correctly so you have one datasource with the grouping - i.e. mark variable in this case - is the only long winded part of this process, and it is there because you have taken the decision to split up like data - which is never a good idea.  Learn to model data well, and coding will become far simpler, more robust, and macro can be forgotten.

[Reeza]

What happens if an ID is in multiple lists? Why do you have commas as the separator, none are needed?

SAS does't think your macro variable exists so you're doing something wrong there but without the full code we can't tell you what, so you'll need to debug it yourself or post your full code and log.