Hi all,
Please , I have modified the initial query ... can you suggest a solution for my requirement .
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
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;
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;
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;
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;
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.