selecting from dataset based on two macro variables

[linlin87]

Hi SAS Community,

I am selecting data from a larger table based on two macro variables, var1 and var2. Both are numeric BEST12.

However, when I run the SQL code I am getting an empty dataset and I can't work out why. Any help would be really appreciated!

proc sql;
select distinct var1 into :v1 separated by "," from dataset1; %put &v1;
quit;

proc sql;
select distinct var2 into :v2 separated by "," from dataset1; %put &v2;
quit;

proc sql; 
create table output1 as 
select *
from dataset2
where var1 in (&v1) and var2 in (&v2);
quit;

I have checked, and in dataset2 the two variables are also BEST12. format. There is no error code, the file is just empty. I have also checked and instances of var1, var2 occur in both dataset1 and dataset2 so I do not know why it is not pulling data from dataset2 based on these two variables.

Example outputs of %put &v1 and %put &v2:

5,8,9,11,13,14,22,27,28,34,52,55,57,66

2.2007E8,2.2008E8,2.2009E8,2.2106E8,2.2111E8

[PaigeMiller · Accepted solution]

This could be the problem

&v2 = 

2.2007E8,2.2008E8,2.2009E8,2.2106E8,2.2111E8

The exponential notation used here indicates that it is not likely matching the exact number in DATASET2, because this truncates the value to an integer rounded to the nearest 10,000 (I think).  I think this might help:

select distinct var2 format=best12. into :v2 separated by "," from dataset1;

I also think maybe you should avoid macro variables when they are not necessary ... data is better handled in data set variables. So this ought to work without macro variables

proc sql; 
    create table output1 as 
    select *
    from dataset2
    where var1 in (select distinct var1 from dataset1) and var2 in (select distinct var2 from dataset1);
quit;

although subqueries like this can be slow on large data sets.

[linlin87]

Thanks Paige. Why does is the macro not automatically stored as best12. given var2 is best12.

[PaigeMiller]

Because as I said, data is best used in DATA steps or PROCs. Creating macro variables from this data means you are converting the data value to a text string (because all macro variables are text strings) and that is the default conversion to text.