Seeking advice on filling columns with multiple criteria

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

Posted in reply to hillcat27

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

View solution in original post


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

Re: Seeking advice on filling columns with multiple criteria

Posted in reply to hillcat27

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: 101

Re: Seeking advice on filling columns with multiple criteria

Posted in reply to hillcat27

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;

Respected Advisor
Posts: 3,156

Re: Seeking advice on filling columns with multiple criteria

Posted in reply to SAS_Bigot

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

Respected Advisor
Posts: 3,156

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: 101

Re: Seeking advice on filling columns with multiple criteria

Very nice! How about this little tweak:

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

Posted in reply to SAS_Bigot

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

Occasional Contributor
Posts: 10

Re: Seeking advice on filling columns with multiple criteria

Posted in reply to hillcat27

Many, Many thanks to all!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 445 views
  • 6 likes
  • 4 in conversation