So, the question i'm about to ask is something i'm trying for the first time in sas. Below is what i want to achieve:
I have two data-sets which have a variable which is common in both. Now that variable has some observation which are common in both the data-sets but also there are some values which are not common. Now, i want the list of those variables which are present in one and not in other for both the data-sets.
i can't think of any proc sql or any other statement/function that can help me achieve what i want. Please help me with this.
Thanks
Several options:
data step:
proc sort data=table1;
by var1;
run;
proc sort data=table2;
by var1;
run;
data exclusions;
merge
table1 (in=t1 keep=var1)
table2 (in=t2 keep=var1)
;
by var1;
if t1 and not t2;
if last.t1; /* avoids duplicates */
run;
SQL:
proc sql;
create table exclusions as
select distinct table1.var1
from table1
where var1 not in (select distinct table2.var1 from table2)
;
quit;
Depending on the size of table2, you could also consider to create a "yes/no" format from it, and use that while reading table1 to decide if observations are kept.
Or one could create a hash object from table2 (needs approx. the same amount of memory as the format method).
Thanks @Kurt_Bremser i tried the proc sql one and it worked smoothly. Though, i nees\d a little clarification on this, the output i will get will be observations of var1 from table1 which are not found in table2. Am i getting it right?
@hiteshchauhan1 wrote:
Thanks @Kurt_Bremser i tried the proc sql one and it worked smoothly. Though, i nees\d a little clarification on this, the output i will get will be observations of var1 from table1 which are not found in table2. Am i getting it right?
Use a UNION operation between two "directional" selections to get both sides. EXCEPT is another way to do the basic selection.
proc sql; create table exclusions as (select distinct table1.var1 from table1 except select distinct table2.var1 from table2) union (select distinct table21.var1 from table2 except select distinct table1.var1 from table1) ; quit;
Thanks @ballardw for the help, working flawlessly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.