SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dennis_oz
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

 

View solution in original post

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
dennis_oz
Quartz | Level 8
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
Garnet | Level 18

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
Quartz | Level 8
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
Super User
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
Quartz | Level 8

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
Garnet | Level 18

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
Super User

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;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1537 views
  • 3 likes
  • 4 in conversation