topic Re: PROC APPEND Alternatives in SAS Data Management

PROC APPEND Alternatives

dharmesh — Fri, 15 Mar 2019 09:39:44 GMT

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

Re: PROC APPEND Alternatives

Criptic — Fri, 15 Mar 2019 10:17:48 GMT

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.

Re: PROC APPEND Alternatives

Kurt_Bremser — Fri, 15 Mar 2019 12:30:28 GMT

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

Re: PROC APPEND Alternatives

Patrick — Sun, 17 Mar 2019 22:15:17 GMT

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

Re: PROC APPEND Alternatives

dharmesh — Mon, 18 Mar 2019 09:10:59 GMT

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

Re: PROC APPEND Alternatives

dharmesh — Mon, 18 Mar 2019 09:25:15 GMT

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

Re: PROC APPEND Alternatives

Patrick — Mon, 18 Mar 2019 09:59:49 GMT

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