topic Re: keep and delete records in SAS Programming

keep and delete records

helloSAS — Thu, 22 May 2014 18:38:32 GMT

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

acct	flag
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

Result table

acct	flag
14710	0
49730	0
15293	1
1147	0
1147	0
13664	1

Re: keep and delete records

CTorres — Thu, 22 May 2014 20:10:24 GMT

Try this:

data have;

input ACCT FLAG;

cards;

14710 1

14710 0

14710 1

49730 1

49730 0

49730 1

15293 1

1147 1

1147 0

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,

Re: keep and delete records

helloSAS — Thu, 22 May 2014 21:05:50 GMT

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

Re: keep and delete records

stat_sas — Thu, 22 May 2014 21:07:10 GMT

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;

Re: keep and delete records

slchen — Thu, 22 May 2014 21:15:53 GMT

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;

Re: keep and delete records

CTorres — Fri, 23 May 2014 12:18:43 GMT

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

Re: keep and delete records

RW9 — Fri, 23 May 2014 12:27:36 GMT

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;

Re: keep and delete records

helloSAS — Fri, 23 May 2014 13:41:34 GMT

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;

Re: keep and delete records

RW9 — Fri, 23 May 2014 13:58:44 GMT

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.

Re: keep and delete records

Patrick — Sat, 24 May 2014 09:01:19 GMT

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=_:);
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;

Re: keep and delete records

Ksharp — Sat, 24 May 2014 11:58:38 GMT

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