- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Jag
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;