Proc SQL inside macro

[mark_ph]

Dear All,

I have the following two datasets:

data big;

     input Obs ID V1;

datalines;

1 1 1

2 2 1

3 2 2

4 3 1

5 3 2

6 3 3

7 4 1

8 4 2

9 4 3

10 5 1;

data small;

     input Obs ID;

datalines;

1 2

2 4

3 5;

I would like to obtain the dataset WANT, which contains all variables in the dataset BIG and all observations in dataset BIG with a value of the variable ID that occurs in the dataset SMALL. That is, I would like to obtain the following:

data want;

     input Obs ID V1;

datalines;

1 2 1

2 2 2

3 4 1

4 4 2

5 4 3

6 5 1;

I have managed to do it by using PROC SQL. This is my attempt:

proc sql;

create table want as

select big.*

from big, small

where big.id = small.id;

quit;

However, I also need to create a macro because I have many datasets BIG. Here, I have problems with syntax of PROC SQL inside a macro. The following code doesn't work.

%macro sampling(big);

proc sql;

create table want as

select &big.*

from &big., small

where &big.dt = small.dt;

quit;

proc append base=final data=want;

proc delete data=want;

run;

%mend;

Any help would be highly appreciated.

[Loko · Accepted solution]

hello,

you have to address the variables from the table like this &big..* therefore you should corect these lines :

select &big..* --> &big..*

&big.dt --> &big..dt

[ballardw]

If I read your description correctly you want a join not a Cartesian product

proc sql;

create table want as

select &big.*, small.id

from &big. left join small

on &big..dt = small.dt;

quit;