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

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

dennis_oz — Mon, 03 Aug 2020 13:00:06 GMT

Hi all,

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

Suppose there exists a dataset --> "have" .
I am getting a new dataset --> "in_coming_file" .
I want the dataset --> "output_1" created to sent to an external vendor . The U denotes updated record and N denotes New record .
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

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

Jagadishkatam — Mon, 03 Aug 2020 12:20:11 GMT

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;

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

Shmuel — Mon, 03 Aug 2020 12:23:00 GMT

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;

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

dennis_oz — Mon, 03 Aug 2020 12:55:46 GMT

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.

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

dennis_oz — Mon, 03 Aug 2020 13:01:46 GMT

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

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

Ksharp — Mon, 03 Aug 2020 13:18:20 GMT

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;

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

dennis_oz — Mon, 03 Aug 2020 13:20:46 GMT

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

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

Shmuel — Mon, 03 Aug 2020 13:33:09 GMT

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;

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

Ksharp — Mon, 03 Aug 2020 13:40:13 GMT

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;