BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

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
10 REPLIES 10
CTorres
Quartz | Level 8

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,

helloSAS
Obsidian | Level 7

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

stat_sas
Ammonite | Level 13

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;

CTorres
Quartz | Level 8

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

slchen
Lapis Lazuli | Level 10

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

helloSAS
Obsidian | Level 7


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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Patrick
Opal | Level 21

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;

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2378 views
  • 3 likes
  • 7 in conversation