DATA Step, Macro, Functions and more

keep and delete records

Reply
Frequent Contributor
Posts: 87

keep and delete records

Hi,

In the below table I would like to keep record with flag =1 only when all records of an acct is flagged 1 else keep record with flag=0. Also, I want to keep all occurences of flag=0. see below for source table and result table. result table is what I'm expecting.

for example:

Source table

acctflag
147101
147100
147101
497301
497300
497301
152931
152931
11471
11470
11470
136641
136641
136641

Result table

acctflag
147100
497300
152931
11470
11470
136641
Regular Contributor
Posts: 180

Re: keep and delete records

Try this:

data have;

input ACCT FLAG;

cards;

14710 1

14710 0

14710 1

49730 1

49730 0

49730 1

15293 1

15293 1

1147  1

1147  0

1147  0

13664 1

13664 1

13664 1

;

run;

data want (drop=save);

   set have (rename=(flag=save));

   retain FLAG;

   by acct notsorted;

   if first.acct then flag=save;

   flag=save and flag;

   if last.acct then output ;

run;

Regards,

Frequent Contributor
Posts: 87

Re: keep and delete records

sorry, but its not giving me the results I want.

Trusted Advisor
Posts: 1,204

Re: keep and delete records

Hi,

Try this one.

proc sort data=have out=one nodup;
by acct flag;
run;

data two(where=(flag=1));
set one;
if acct-lag(acct)=0 and flag=1 then delete;
run;

data want;
set two have(where=(flag=0));
run;

Regular Contributor
Posts: 180

Re: keep and delete records

You are rigth. I did not see the words "all occurrences of zeros"

Super Contributor
Posts: 275

Re: keep and delete records

data have;
input acct    flag;
cards;
14710    1
14710    0
14710    1
49730    1
49730    0
49730    1
15293    1
15293    1
1147    1
1147    0
1147    0
13664    1
13664    1
13664    1
;
run;
proc sort data=have;
by acct descending flag;
run;

data want;
  do i=1 by 1 until (last.acct);
  set have;
  by acct;
  array vars[100] _temporary_;
  vars(i)=flag;
  count+1;
  if last.acct then do;
     if count(cats(of vars(*)),'1')=count then output;
     else do;
         do j=1 to count(cats(of vars(*)),'0');    
           output;
         end;
      end;
     end;
   end;
call missing(of vars(*),count);
drop i j count;
run;

Super User
Super User
Posts: 7,401

Re: keep and delete records

How about this:

proc sql;

  create table WANT as

  select  distinct *

  from    HAVE

  where   ACCT not in (select distinct ACCT from HAVE where FLAG=0)

  union all

  select  *

  from    HAVE

  where   FLAG=0

  order by ACCT

;

quit;

Frequent Contributor
Posts: 87

Re: keep and delete records


Thanks for this solution. Its close, but I'm still not getting unique values of flag=1. this is what I did and I'm getting intended results. Can you please tell me if I can do this in a single step?

proc sql;
create table ph_hba3 as
select distinct * from ph_hba1
where acct_id not in (select distinct acct_id from ph_hba1 where flag=0) order by acct_id;
quit;


proc sort data=ph_hba3 nodupkey ;by acct_id;run;


proc sql;
create table ph_hba4 as
select distinct * from ph_hba3
union all
select * from ph_hba1 where flag=0 order by acct_id;
quit;

Super User
Super User
Posts: 7,401

Re: keep and delete records

For the data you provided in your OP, my code gives the response per your expected output.  If it is not doing what you want then you need to identify what you are not covering in your test data above.  For example I assumed from your test data that select distinct * would be adequate as there is only that value and the one I am grouping.  However if you have more than those variables then you need to modify the code accordingly to select distinct -list of variables to identify a unique -.

To clarify the * (asterix), means all variables in the used datasets.  It is advised that you specify variables explicitly to avoid these types of things.

Respected Advisor
Posts: 3,887

Re: keep and delete records

Here a data step approach which should perform reasonably well.

data have;
input acct $ flag :$1.;
datalines;
14710 1
14710 0
14710 1
49730 1
49730 0
49730 1
15293 1
15293 1
1147 1
1147 0
1147 0
13664 1
13664 1
13664 1
;
run;

data want(drop=_Smiley Happy;
  set have;
  if _n_=1 then
    do;
      dcl hash h(dataset:"have(where=(flag='0'))");
      _rc=h.defineKey('acct');
      _rc=h.defineDone();
    end;

  if flag='0' then output;
  else if flag='1' and h.check() ne 0 then
    do;
      output;
      _rc=h.add();
    end;
run;

Super User
Posts: 9,676

Re: keep and delete records

data have;
input ACCT FLAG;
cards;
14710 1
14710 0
14710 1
49730 1
49730 0
49730 1
15293 1
15293 1
1147  1
1147  0
1147  0
13664 1
13664 1
13664 1
;
run;
proc sql;
 create table want as
  select * from have where flag=0
   union all
  select distinct * from have group by acct having sum(flag=1)=count(*);
quit;

Xia Keshan

Ask a Question
Discussion stats
  • 10 replies
  • 442 views
  • 3 likes
  • 7 in conversation