topic how to get distinct value after vertical merge in SAS Programming

how to get distinct value after vertical merge

Alexxxxxxx — Sat, 02 Mar 2019 17:47:23 GMT

Dear all,

how can I get distinct value by vertical merging

for example

table a

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.

Re: how to get distinct value after vertical merge

novinosrin — Sun, 03 Mar 2019 01:47:08 GMT

proc sql;
create table want as
  select * from a
   union 
   select * from b;
quit

Re: how to get distinct value after vertical merge

novinosrin — Sun, 03 Mar 2019 01:48:21 GMT



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;

Re: how to get distinct value after vertical merge

mkeintz — Sun, 03 Mar 2019 02:12:06 GMT

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.

Re: how to get distinct value after vertical merge

Alexxxxxxx — Sun, 03 Mar 2019 12:18:32 GMT

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?

Re: how to get distinct value after vertical merge

mkeintz — Sun, 03 Mar 2019 18:19:13 GMT

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.

Re: how to get distinct value after vertical merge

Patrick — Sun, 03 Mar 2019 21:30:39 GMT

@Alexxxxxxx

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;