PROC APPEND Alternatives

[dharmesh]

Hi team, I need help to append non matching data in 3 tables  Table 1 Unique id Value 1 a 2 b Table 2 Unique id Value 1 a 3 c Table 3 Unique id Value 3 c 4 d Result expected Unique id Value 1 a 2 b 3 c 4 d SAS ega version 7.2 Can I do this in SAS ega in advance expressions or by some join or using advance expression  Please guide  Thanks in advance. Dharmesh

[Criptic · Accepted solution]

Hey @dharmesh,

for future posts please add the data as a data step with datalines in your post to speed up evaluation.

If you don't need any logic like that one table is leading or that the id value combinations could be different you can simply use:

proc sql;
 create table work.want as
 select distinct * from work.have;
 quit;

Where want is the table that contains all three of the tables just appended.

[Kurt_Bremser]

---

@dharmesh wrote:
Hi team, I need help to append non matching data in 3 tables  Table 1 Unique id Value 1 a 2 b Table 2 Unique id Value 1 a 3 c Table 3 Unique id Value 3 c 4 d Result expected Unique id Value 1 a 2 b 3 c 4 d SAS ega version 7.2 Can I do this in SAS ega in advance expressions or by some join or using advance expression  Please guide  Thanks in advance. Dharmesh

---

Are you seriously trying to insult us by posting like this? One ugly spaghetti, almost unreadable, and if data is included somewhere, it's unusable.

Please put a little more effort into your question.

[dharmesh]

Well I didn't mean to. Regret for putting you in trouble.

I could preview the post as I had mentioned in my query and hence was posted .. But I will try to be careful 

Thanks,

Dharmesh

[Patrick]

@dharmesh 

What about EG's Append Table task and then a Proc Sort to remove duplicates.

Alternatively use a SQL UNION CORR without keyword ALL. This will also concatenated and dedup your data.

[dharmesh]

Thanks Patric 🙂

It worked using below code

DATA FINAL_BASE;
SET table 1 table2 table3;
RUN;
PROC SORT DATA=FINAL_BASE;BY AGREEMENTNO;RUN;
DATA FINAL_UNQ;
SET FINAL_BASE;
BY AGREEMENTNO;
IF FIRST.AGREEMENTNO;
RUN;

PROC SORT DATA=FINAL_UNQ;BY PAN_NO;RUN;

Regards,

Dharmesh

[Patrick]

@dharmesh 

Just to show you how that would look like using SQL UNION CORR.

data class1 class2 class3;
  set sashelp.class(where=(name='Alfred'));
  output;
  output;
  name='Other';
  output class2;
run;

proc sql;
  create table want as
    select * from class1
    union corr
    select * from class2
    union corr
    select * from class3
    ;
quit;

proc print data=want;
run;

This only works if you've got the same columns in all source tables AND if you really want to de-duplicate records which is different to just select the first record per sort key (where other variables within the same key still could have different values).