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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.