Dear all,
how can I get distinct value by vertical merging
for example
table a
A,1
A,1
B,1
table b
A,1
C,1
I expect to get the
table c
A,1
B,1
C,1
Could you please give me some suggestions about this?
thanks in advance.
If each of your data sets (i.e. tables) is sorted b LETR,NUMB (the varnames I will assign), you can use the sas data step, as follows:
data a;
infile datalines dlm=',';
input letr $1. nmbr ;
datalines;
A,1
A,1
B,1
run;
data b;
infile datalines dlm=',';
input letr $1. nmbr ;
datalines;
A,1
C,1
run;
data want;
set a b;
by letr nmbr;
if first.nmbr;
run;
This will be the fastest approach, but it requires each data set to be sorted by LETR/NUMB.
proc sql;
create table want as
select * from a
union
select * from b;
quit
data a;
infile cards dsd;
input v1 $ v2 ;
cards;
A,1
A,1
B,1
;
data b;
infile cards dsd;
input v1 $ v2 ;
cards;
A,1
C,1
;
proc sql;
create table want as
select * from a
union
select * from b;
quit;
If each of your data sets (i.e. tables) is sorted b LETR,NUMB (the varnames I will assign), you can use the sas data step, as follows:
data a;
infile datalines dlm=',';
input letr $1. nmbr ;
datalines;
A,1
A,1
B,1
run;
data b;
infile datalines dlm=',';
input letr $1. nmbr ;
datalines;
A,1
C,1
run;
data want;
set a b;
by letr nmbr;
if first.nmbr;
run;
This will be the fastest approach, but it requires each data set to be sorted by LETR/NUMB.
Dear mkeintz,
thanks for your advice,
What should I do if the 'nmbr' variables are different as well? for example, A,2.
data a;
infile datalines dlm=',';
input letr $1. nmbr ;
datalines;
A,1
A,2
B,1
run;
data b;
infile datalines dlm=',';
input letr $1. nmbr ;
datalines;
A,1
C,1
run;
could you give me some suggestions about this?
It's a computer program. Try it with your revised data and see the results. Then, if something is unexpected, you can point it out and ask why.
If you're after unique rows from two or more tables then use SQL UNION syntax as already posted by @novinosrin
proc sql;
create table want as
select * from a
union corr
select * from b;
quit;
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 25. Read more here about why you should contribute and what is in it for you!
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.