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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: