topic Re: identifying new enteries in SAS Programming

identifying new enteries

pacman94 — Fri, 11 Mar 2022 16:40:15 GMT

I have the following dataset. What I would like to do is find NEW enteries based on ID and instance # into the new export but retain the new information

old:

ID	group	instance	term	term_2
1	1	1	AAAA	one
1	2 indirect	2	CCCC	seven
1	2 indirect	3	DDDD	seven
2	1	1	ASFD	two
2	2 indirect	2	DDA	three
3	2 direct	1	ERQE	one
4	1	1	ABCD	seven
4	1	2	ABCD	five

for exmaple this is the new dataset that I would like to create. If ID and instance doesn't exist "old" dataset, then I would like to flagT the entries into the another dataset. new export:

ID	group	instance	term	term_2
1	1	1	AAAA	one
1	2 indirect	2	CCCC	seven
1	2 indirect	3	DDDD	seven
1	2 indirect	4	DFDD	two
2	1	1	ASFD	two
2	2 indirect	2	DDA	three
3	2 direct	1	ERQE	one
4	1	1	ABCD	seven
4	1	2	ABCD	five
4	2 direct	3	CCCC	one
5	1	1	ERQE	seven

Want

ID	group	instance	term	term_2	flagT
1	1	1	AAAA	one	0
1	2 indirect	2	CCCC	seven	0
1	2 indirect	3	DDDD	seven	0
1	2 indirect	4	DFDD	two	1
2	1	1	ASFD	two	0
2	2 indirect	2	DDA	three	0
3	2 direct	1	ERQE	one	0
4	1	1	ABCD	seven	0
4	1	2	ABCD	five	0
4	2 direct	3	CCCC	one	1
5	1	1	ERQE	seven	1

I did the following:

proc sql;

create table old_flag as

select id, max(instance) as instance

from old group by id;

quit;

proc sql;

create table new_flag as

select id, max(instance) as instance

from new group by id;

quit;

not sure what to do next

Re: identifying new enteries

HarrySnart — Fri, 11 Mar 2022 17:25:15 GMT

Hi @pacman94 ,

Is this what you're after?

*Load data;
data work.old;
length id instance 8 group term term_2 $20;
input id  group instance  term  term_2 ;
infile datalines dlm="," dsd;
datalines;
1,	1,	1,	AAAA,	one
1,	2 indirect,	2,	CCCC,	seven
1,	2 indirect,	3,	DDDD,	seven
2,	1,	1,	ASFD,	two
2,	2 indirect,	2,	DDA,	three
3,	2 direct,	1,	ERQE,	one
4,	1,	1,	ABCD,	seven
4,	1,	2,	ABCD,	five
;

data work.new;
length id instance 8 group term term_2 $20;
input id  group instance  term  term_2 ;
infile datalines dlm="," dsd;
datalines;
1,	1,	1,	AAAA,	one
1,	2 indirect,	2,	CCCC,	seven
1,	2 indirect,	3,	DDDD,	seven
1,	2  indirect,	4,	DFDD,	two
2,	1,	1,	ASFD,	two
2,	2 indirect,	2,	DDA,	three
3,	2 direct,	1,	ERQE,	one
4,	1,	1,	ABCD,	seven
4,	1,	2,	ABCD,	five
4,	2 direct,	3,	CCCC,	one
5,	1,	1,	ERQE,	seven
;

*Find new records;

proc sql;
create table work.flag_1 as
select *, 1 as flagT from
(   select *
      from work.new
   except
   select *
      from work.old);
	quit;

*Add flag 0 to old records;
data work.old;
set work.old;
flagT = 0;
run;

*Merge tables;
data work.want;
set work.flag_1 work.old;
run;

Re: identifying new enteries

Ksharp — Sat, 12 Mar 2022 11:12:03 GMT

data work.old;
length id instance 8 group term term_2 $20;
input id  group instance  term  term_2 ;
infile datalines dlm="," dsd;
datalines;
1,	1,	1,	AAAA,	one
1,	2 indirect,	2,	CCCC,	seven
1,	2 indirect,	3,	DDDD,	seven
2,	1,	1,	ASFD,	two
2,	2 indirect,	2,	DDA,	three
3,	2 direct,	1,	ERQE,	one
4,	1,	1,	ABCD,	seven
4,	1,	2,	ABCD,	five
;

data work.new;
length id instance 8 group term term_2 $20;
input id  group instance  term  term_2 ;
infile datalines dlm="," dsd;
datalines;
1,	1,	1,	AAAA,	one
1,	2 indirect,	2,	CCCC,	seven
1,	2 indirect,	3,	DDDD,	seven
1,	2  indirect,	4,	DFDD,	two
2,	1,	1,	ASFD,	two
2,	2 indirect,	2,	DDA,	three
3,	2 direct,	1,	ERQE,	one
4,	1,	1,	ABCD,	seven
4,	1,	2,	ABCD,	five
4,	2 direct,	3,	CCCC,	one
5,	1,	1,	ERQE,	seven
;

data want;
 if _n_=1 then do;
   if 0 then set old;
   declare hash h(dataset:'old');
   h.definekey(all:'yes');
   h.definedone();
 end;
set new;
flagT=1;
if h.check()=0 then flagT=0;
run;