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
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.