Proc SQL inside macro

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Proc SQL inside macro

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.


Accepted Solutions
Solution
‎09-12-2014 10:34 AM
Super Contributor
Posts: 305

Re: Proc SQL inside macro

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

View solution in original post


All Replies
Solution
‎09-12-2014 10:34 AM
Super Contributor
Posts: 305

Re: Proc SQL inside macro

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

Grand Advisor
Posts: 10,210

Re: Proc SQL inside macro

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 381 views
  • 0 likes
  • 3 in conversation