- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
sorry, but its not giving me the results I want.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You are rigth. I did not see the words "all occurrences of zeros"
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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