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.
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
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.