topic Re: Data Logic in SAS Programming

Data Logic

Almoha — Mon, 30 Jun 2014 05:12:27 GMT

All,

I have the following data structure .

ID time(hr) flag1 flag2

11 2 Y

11 4 Y

11 6

11 8 Y

11 10

22 2 Y

22 4

22 6 Y

22 8 Y

22 10 Y

If 2 or more consecutive time points(time) have a "Y " for flag1 , then I am trying to assig the "Y" to flag2 field as below

The output I am looking for is

ID time(hr) flag1 flag2

11 2 Y Y

11 4 Y Y

11 6

11 8 Y

11 10

22 2 Y

22 4

22 6 Y Y

22 8 Y Y

22 10 Y Y

can anyone help me with the logic?

Re: Data Logic

andreas_lds — Mon, 30 Jun 2014 07:37:25 GMT

You can use the method called "Look-Ahead and Look-Back" described in Look-Ahead and Look-Back - sasCommunity

Re: Data Logic

Kurt_Bremser — Mon, 30 Jun 2014 08:19:44 GMT

I came to the following solution:

data comp (keep=id h1 flag2 rename=(h1=time));

set have;

by id;

retain flag2 'Y';

flag1_l1 = lag(flag1);

flag1_l2 = lag2(flag1);

time_l1 = lag(time);

id_l1 = lag(id);

id_l2 = lag2(id);

if not first.id then do;

if flag1 = 'Y' and flag1_l1 = 'Y' then do;

if flag1_l2 ne 'Y' and (id_l2 = id or id_l2 = .) then do;

h1 = time_l1;

output;

end;

h1 = time;

output;

end;

run;

data want;

merge have comp;

by id time;

run;

Re: Data Logic

Ksharp — Mon, 30 Jun 2014 12:08:56 GMT

If I understood your question rightly.

data have;
infile cards truncover;
input ID   time   flag1  $ ;
cards;
11    2           Y
11    4           Y
11    6  
11    8           Y      
11   10        
22   2            Y
22   4
22   6            Y      
22   8            Y      
22   10           Y     
;
run; 
data want;
 set have;
 by id flag1 notsorted;
 if not (first.flag1 and last.flag1) then flag2='Y';
run;

Xia Keshan

Re: Data Logic

Kurt_Bremser — Mon, 30 Jun 2014 12:30:11 GMT

But that would also mark 2 successive empty flag1 with flag2 = 'Y'.

Re: Data Logic

Astounding — Mon, 30 Jun 2014 13:10:48 GMT

A nice problem to start the week. One approach:

data want;

yes_count=0;

do until (last.id or flag1=' ');
set have;

by id;

if flag1='Y' then yes_count + 1;

end;

do until (last.id or flag1=' ');

set have;

by id;

if flag1='Y' and yes_count >= 2 then flag2='Y';

else flag2=' ';

output;

end;

drop yes_count;

run;

The top loop reads a bunch of records, and determines when FLAG2 should be Y for this records. The bottom loop reads the same records and assigns FLAG2.

Good luck.

Re: Data Logic

Kurt_Bremser — Mon, 30 Jun 2014 13:22:55 GMT

I knew there was a DWO loop solution, just couldn't get myself to find it!

But you need to compare >=2, or you will only catch three Ys in succession

Edit: saw you already fixed it.

Re: Data Logic

Astounding — Mon, 30 Jun 2014 13:23:09 GMT

Yes, but that is easily correctible:

if (flag1='Y') and not (first.flag1 and last.flag1) then flag2='Y';

Re: Data Logic

Astounding — Mon, 30 Jun 2014 13:23:46 GMT

Thanks, I did change that once I noticed.

Re: Data Logic

Haikuo — Mon, 30 Jun 2014 13:33:37 GMT

OK, look ahead and look back:

data have;

infile cards truncover;

input id time flag $;

cards;

11 2 Y

11 4 Y

11 6

11 8 Y

11 10

22 2 Y

22 4

22 6 Y

22 8 Y

22 10 Y

;

data want;

set have;

by id;

set have(firstobs=2 keep=flag rename=flag=_f) have(obs=1 drop=_all_);

if not last.id and flag='Y' and _f='Y' or flag='Y' and lag(flag)='Y' then

flag2='Y';

drop _f;

run;

Haikuo

Re: Data Logic

Ksharp — Tue, 01 Jul 2014 11:53:12 GMT

Astounding have my word . Thanks, Astounding .