BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dharmesh
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
Criptic
Lapis Lazuli | Level 10

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.

View solution in original post

6 REPLIES 6
Criptic
Lapis Lazuli | Level 10

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
Super User

@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
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5

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
Opal | Level 21

@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).

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1155 views
  • 0 likes
  • 4 in conversation