SAS PROC SQL Reference list of macros

Question

I am having trouble with the syntax when trying to reference a macro variable.

I have a subset of ID numbers and a dataset with a quantitative variable xxx associated by IDnum:

data IDnumlist;
    input IDnum;
cards;
123
456
789
;
run;

data info;
    input IDnum xxx;
cards;
123 2
123 5
456 3
789 1
789 4
555 9
;
run;

I want to summarize the data in the info dataset, but not for IDnum=555, since that is not in my subset. So my data set would look like this:

IDnum  xxx_count   xxx_sum
123    2           7
456    1           3
789    2           5

Here is my attempt so far:

proc sql noprint; 
    select count(*) 
        into :NObs 
    from IDnumlist; 
    select IDnum 
        into :IDnum1-:IDnum%left(&NObs) 
    from IDnumlist; 
quit;

proc sql;
    create table want as
    select  IDnum,
            count(xxx) as xxx_count,
            sum(xxx) as xxx_sum
    from info
    where IDnum in (&IDnum1-IDnum%left(&NObs))
    group by 1;
run;

What am I doing wrong?

Answer 1

Why are you using macro variables for this? This is what a join is for, or a subquery, or who knows how many other better ways to do this.

proc sql;
create table want as
  select info.idnum, count(xxx) as xxx_count, sum(xxx) as xxx_sum
  from info inner join idnumlist
   on info.idnum=idnumlist.idnum
  group by info.idnum;
quit;

The specific problem in your above code is that you can't use 'macro variable lists' like you can data step lists. You could in theory list them individually, but better would be to do the select into differently.

proc sql noprint; 
    select IDnum 
        into :IDnumlist separated by ','
    from IDnumlist; 
quit;

Then all of the values are in &idnumlist. and can be used directly with the in operator:

where idnum in (&idnumlist.)