Seeking advice on filling columns with multiple criteria

Solved
Occasional Contributor
Posts: 10

Seeking advice on filling columns with multiple criteria

Greetings,

I have read several posts on how to "fill down" columns but none have gotten me any closer to this particular situation.  Any help would be greatly appreciated.

The format of my data are (Columns A, B, C):

A    B    C

F   S1

F   S2    0

F   S2    1

F   S3

---------------

G   S1

G   S2    0

G   S3

---------------

H   S1

H   S2    1

H   S2    1

H   S3

What I need:  For each sequence in Column A, the blank variables in Column C must read '1' if at least one of Column B's S2 variables = 1 in Column C;  and, if all of Column B's S2 variables = 0 in Column C, then the associated S1 and S3 variables for that sequence must also = 0.

Therefore, in this example, I would have:

A    B    C

F   S1    1

F   S2    0

F   S2    1

F   S3    1

---------------

G   S1    0

G   S2    0

G   S3    0

---------------

H   S1    1

H   S2    1

H   S2    1

H   S3    1

Many thanks for he help!

Accepted Solutions
Solution
‎05-17-2012 12:34 AM
Super Contributor
Posts: 1,636

Re: Seeking advice on filling columns with multiple criteria

data have;

infile cards missover;

input a\$ b\$ c\$;

cards;

F   S1

F   S2    0

F   S2    1

F   S3

G   S1

G   S2    0

G   S3

H   S1

H   S2    1

H   S2    1

H   S3

;

proc sql;

create table temp as select distinct a from have

group by a

having sum(cats(b,c)='S21')>0; quit;

data want;

merge have(in=h) temp(in=t);

by a;

if c=' ' then do;

if t then c='1';

else c='0';

end;

run;

proc print;run;

1    F    S1    1

2    F    S2    0

3    F    S2    1

4    F    S3    1

5    G    S1    0

6    G    S2    0

7    G    S3    0

8    H    S1    1

9    H    S2    1

10    H    S2    1

11    H    S3    1

All Replies
Solution
‎05-17-2012 12:34 AM
Super Contributor
Posts: 1,636

Re: Seeking advice on filling columns with multiple criteria

data have;

infile cards missover;

input a\$ b\$ c\$;

cards;

F   S1

F   S2    0

F   S2    1

F   S3

G   S1

G   S2    0

G   S3

H   S1

H   S2    1

H   S2    1

H   S3

;

proc sql;

create table temp as select distinct a from have

group by a

having sum(cats(b,c)='S21')>0; quit;

data want;

merge have(in=h) temp(in=t);

by a;

if c=' ' then do;

if t then c='1';

else c='0';

end;

run;

proc print;run;

1    F    S1    1

2    F    S2    0

3    F    S2    1

4    F    S3    1

5    G    S1    0

6    G    S2    0

7    G    S3    0

8    H    S1    1

9    H    S2    1

10    H    S2    1

11    H    S3    1

Frequent Contributor
Posts: 103

Re: Seeking advice on filling columns with multiple criteria

data have;

infile cards missover;

input a\$ b\$ c;

cards;

F   S1

F   S2    0

F   S2    1

F   S3

G   S1

G   S2    0

G   S3

H   S1

H   S2    1

H   S2    1

H   S3

;

run;

proc sql;

create table want as

select

x.a,

x.b,

coalesce( x.c, (not(missing(y.a))) ) as  c

from

have  x

left join

( select

a

from have

where b = 'S2'

group by a

having sum(c)>0 )  y

on

a.a = b.a

order by 1,2

;

quit;

Posts: 3,167

Re: Seeking advice on filling columns with multiple criteria

This can also be done using 2XDOW;

data have;

infile cards truncover;

input a\$ b\$ c;

cards;

F S1

F S2 0

F S2 1

F S3

G S1

G S2 0

G S3

H S1

H S2 1

H S2 1

H S3

;

data want;

do until (last.a);

set have;

by a notsorted;

if (b='S2' and c=1) then flag=1;

end;

do until (last.a);

set have;

by a notsorted;

if missing (c) then c=ifn(flag=1, 1,0);

output;

end;

drop flag;

run;

proc print;run;

Haikuo

Posts: 3,167

Re: Seeking advice on filling columns with multiple criteria

Recently learned form Mike zdeb at SAS-L, it will only use 1X DOW;

data want;

do until (last.a);

set have (in=h1) have;

by a ;

if (h1 and b='S2' and c=1) then flag=1;

if not h1 and missing (c) then do;  if flag=1 then c=1;  else c=0;  end;

if not h1 then output;

end;

drop flag;

run;

Haikuo

Frequent Contributor
Posts: 103

Re: Seeking advice on filling columns with multiple criteria

data want;

flag=0;

do until (last.a);

set have (in=h1) have;

by a ;

if (h1 and b='S2' and c=1) then flag=1;

if not h1 then do;

c=coalesce(c,flag);

output;

end;

end;

drop flag;

run;

Occasional Contributor
Posts: 10

Re: Seeking advice on filling columns with multiple criteria

Like a gem... I'll use this one from now on.  Thanks again, and nice work

Occasional Contributor
Posts: 10