Base SAS - how to capture changed data and new record as in a delta file ?

[dennis_oz]

Hi all,

Please , I have modified the initial query ... can you suggest a solution for my requirement .

1. Suppose there exists a dataset -->  "have" .
2. I am getting a new dataset -->                "in_coming_file" .
3. I want the dataset --> "output_1" created to sent to an external vendor .  The U denotes updated record and N denotes New record .
4. new "have " dataset will be replaced by dataset --> "final_have" 

data have;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
Gilbert Auditor 03JAN018
;

data in_coming_file ;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018
;

final_have
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018

output_1
John Sales 02JAN2018 U
Mary Finance 02JAN2018 U
Jake Finance 02JAN2018 N

[Jagadishkatam]

Please try

data have;
   input name $ dept $ date date9.;
   format date date9.;
   datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
;

data in_coming_file ;
   input name $ dept $ date date9.;
   format date date9.;
   datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
;


proc sort data=have;
by name dept date;
run;

proc sort data=in_coming_file;
by name dept date;
run;

data want;
merge have(in=a) in_coming_file(in=b);
by name  ;
if b and not a then flag='N';
if a and b then flag='U';
run;

[dennis_oz]

my bad , I have one more condition in the data .. please find below

data have;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
Gilbert Auditor 03JAN018
;

data in_coming_file ;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018
;

final_have
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018

output_1
John Sales 02JAN2018 U
Mary Finance 02JAN2018 U
Jake Finance 02JAN2018 N


Please can you suggest how the code will look now.

[Shmuel]

Check next code:

data final_have;
   update have (in=in_old)
       in_comin_file(=in_trans)
  ;
   by name dept;
      if  in_old and not in_trans then flag = ' '; else
      if in_old and in_trans then flaug = 'U'; else flag = 'N';
run;

[dennis_oz]

my bad, I had another condition in the input data ..
Please can you advice now.

data have;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
Gilbert Auditor 03JAN018
;

data in_coming_file ;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018
;

final_have
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018

output_1
John Sales 02JAN2018 U
Mary Finance 02JAN2018 U
Jake Finance 02JAN2018 N

[Ksharp]

data have(index=(name));
   input name $ dept $ date date9.;
   format date date9.;
   datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
;

data in_coming_file(index=(name)) ;
   input name $ dept $ date date9.;
   format date date9.;
   datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
;

proc sort data=have out=_have;by name;run;
proc sort data=in_coming_file out=_in_coming_file;by name;run;

proc compare data=_have compare=_in_coming_file out=noequal outnoequal noprint;
id name;
var dept date;
run;


proc sql;
create table want as
select *,case when exists(select * from noequal where name=a.name) then 'U' 
   when not exists(select * from have where name=a.name) then 'N' 
   else ' ' end as flag
 from  in_coming_file as a;
quit;

[dennis_oz]

hi ksharp,

I have modified my input file to below 

Please can you advice now.

data have;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
Gilbert Auditor 03JAN018
;

data in_coming_file ;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018
;

final_have
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018

output_1
John Sales 02JAN2018 U
Mary Finance 02JAN2018 U
Jake Finance 02JAN2018 N

[Shmuel]

The only change to my code is sorting the input datasets before update.

I assume names are department are case equal.

proc sort data=have; by name dept; run;
proc sort data=in_coming_file; by name dept; run;

data final_have;
   update have (in=in_old)
       in_coming_file(=in_trans)
  ;
   by name dept;
      if  in_old and not in_trans then flag = ' '; else
      if in_old and in_trans then flaug = 'U'; else flag = 'N';
run;

[Ksharp · Accepted solution]

EDIT to fix a problem.

data have;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 01JAN2018
Mary Acctng 01JAN2018
Gilbert Auditor 03JAN018
;

data in_coming_file ;
input name $ dept $ date date9.;
format date date9.;
datalines;
John Sales 02JAN2018
Mary Finance 02JAN2018
Jake Finance 02JAN2018
Gilbert Auditor 03JAN018
;

proc sql;
create table updated as
select * from have
except 
select * from in_coming_file;

create table new as
select * from in_coming_file
 where name not in (select distinct name from have);

create table want as
select *,'U' as flag from updated where name in (select distinct name from in_coming_file)
union
select *,'N' as flag from new;
quit;