topic Re: Logic help required. in SAS Programming

Logic help required.

Saravanan — Fri, 26 Oct 2012 01:55:15 GMT

Hi Friends,

I need the logic help, below is the table, below is input statement and data which is grouped by sno.

input sno A B flag;

cards;

1 0 1 .

1 1 1 1

1 10 2 10

1 1 1 1

1 10 2 10

2 0 1 .

2 10 1 10

2 1 1 1

2 10 1 10

2 1 1 1

3 10 1 10

3 99 1 .

3 1 1 1

3 10 2 10

4 1 1 1

4 0 1 .

4 10 2 10

;

The logic that has to be accomplished :, within each by group (grouped by sno), if the first record has flag=1 then it should be output and rest of the records within the group should be ignored.

if the first record(grouped by sno) has flag=10, then it should proceed to the subsequent record and check for the flag till it finds either flag= 1 or 10 for first time with in the By group, if the found flag is 1 then rest of the records within the By group should be ignored, else if the found flag is 10 then all records including this record and rest of the records should be ignored with in the By group.

if the first record(group by sno) has neither flag= 1 or 10 then, it should continue searching for either flag=1 or 10 for the first time within the By group, if the found flag is 1 then rest of the records within the By group should be ignored, else if the found flag is 10 then all records including this record and rest of the records should be ignored with in the By group

The output for the above data should look like,

sno A B flag

1 0 1 .

1 1 1 1

2 0 1 .

3 10 1 10

3 99 1 .

3 1 1 1

4 1 1 1

Your help is much appreciated.

Thanks,

Saravanan

Re: Logic help required.

RichardinOz — Fri, 26 Oct 2012 04:15:04 GMT

I found the logic for this very difficult to follow and execute until I understood that the effect of flag = 10 differed whether it was the first record in the group (when in effect it behaves like "other") or later in the sequence. So I defined xflag = flag except when it was the first record and flag=10, in which case xflag = 0. I defined snogroup to be the value of sno for the group (needed unless you do the logic in a separate data step with BY group processing), discard to be the effect on subsequent records in the group, and firstflag to be the first instance of xflag = 1 or 10 in the group.

While this answer delivers the required results for the data specified it wold not vastly surprise me if there was some combination of input records where the result was not as expected.

Richard in Oz

Data want ;

input sno A B flag;

Retain snogroup 0

discard 0

firstflag 0

;

xflag = flag ;

If NOT(snogroup = sno)

Then

Do ;

snogroup = sno ;

discard = 0 ;

firstflag = 0 ;

If flag = 10

then xflag = 0 ;

End ;

Select (xflag) ;

When (1)

Do ;

If Firstflag = 0

Then

Do ;

Firstflag = 1 ;

Output ;

Discard = 1 ;

End ;

If Firstflag = 10

Then

Do ;

Discard = 1 ;

End ;

When (10)

Do ;

If Firstflag = 0

Then

Do ;

Firstflag = 10 ;

End ;

If Firstflag = 10

Then

Do ;

Discard = 1 ;

End ;

Otherwise

Do ;

If Discard = 0

Then Output ;

End ;

Drop snogroup Discard Firstflag xflag ;

cards;

1 0 1 .

1 1 1 1

1 10 2 10

1 1 1 1

1 10 2 10

2 0 1 .

2 10 1 10

2 1 1 1

2 10 1 10

2 1 1 1

3 10 1 10

3 99 1 .

3 1 1 1

3 10 2 10

4 1 1 1

4 0 1 .

4 10 2 10

;

sno	A	B	flag
1	0	1	.
1	1	1	1
2	0	1	.
3	10	1	10
3	99	1	.
3	1	1	1
4	1	1	1

Re: Logic help required.

Saravanan — Fri, 26 Oct 2012 12:54:36 GMT

Hi Richard,

Thanks for the response. The logic works for above data and since you warned for some combination of records it might not work, i have changed the input data to accomodate whether 10 comes in the first records or in the later records. I have added an extra flag named flag1 which will let you know if 10 comes as a first record. is it now possible to come up with a robust logic with this modified data?. Thanks for your help

the logic that has to be accompished is:

within each by group (grouped by sno), if the first record has flag=1 & flag1=. then it should be output and rest of the records within the group should be ignored.

if the first record(grouped by sno) has (flag=10 & flag1=1) or (flag not in (1,10) & flag1=.), then it should proceed to the subsequent record and check for the flag till it finds either flag= 1 or 10 for first time with in the By group, if the found flag is 1 then rest of the records within the By group should be ignored, else if the found flag is 10 then all records including this record and rest of the records should be ignored with in the By group.

sno A B flag flag1

1 0 1 . .

1 1 1 1 .

1 10 2 10 .

1 1 1 1 .

1 10 2 10 .

2 0 1 . .

2 10 1 10 .

2 1 1 1 .

2 10 1 10 .

2 1 1 1 .

3 10 1 10 1

3 99 1 . .

3 1 1 1 .

3 10 2 10 .

4 1 1 1 .

4 0 1 . .

4 10 2 10 .

now the output will look like,

sno A B flag

1 0 1 . .

1 1 1 1 .

2 0 1 . .

3 10 1 10 1

3 99 1 . .

3 1 1 1 .

4 1 1 1 .

Thanks for all the help. it will be great to have the robust logic achieved.

Saravanan

Re: Logic help required.

Saravanan — Fri, 26 Oct 2012 16:19:21 GMT

Hi ,

I came up with the below code which seems to work robustly. Thanks for your help. Much appreciated.

data want(drop=_:);

set have;

by sno;

retain _twoflag _threeflag;

if first.sno & flag=1 & flag1=. then

do;

output;

_twoflag=0;

end;

else if (first.sno & flag=10 & flag1=1) or (first.sno & flag not in (1,10) & flag1=.) then

do;

output;

_twoflag=1;

_threeflag=0;

end;

if _twoflag=1 & first.sno ne 1 then

do;

if _threeflag=0 & flag not in (1,10) then output;

else if _threeflag=0 & flag=1 then

do;

output;

_threeflag=1;

end;

else if flag=10 then _threeflag=1;

end;

run;

Thanks all who tried to help me. much appreciated.

Saravanan

Re: Logic help required.

Haikuo — Fri, 26 Oct 2012 17:07:14 GMT

Hi,

I hope I have not oversimplified your problem. From my understanding, you only have the following two scenarios that flagging the obs not be output:

1. Any obs that is AFTER flag=1 within the same group;

2. Any obs that is INCLUDED AND AFTER (flag=10 and not the first obs in the group).

If so, we can have the following code:

data have;

input sno A B flag;

cards;

1 0 1 .

1 1 1 1

1 10 2 10

1 1 1 1

1 10 2 10

2 0 1 .

2 10 1 10

2 1 1 1

2 10 1 10

2 1 1 1

3 10 1 10

3 99 1 .

3 1 1 1

3 10 2 10

4 1 1 1

4 0 1 .

4 10 2 10

;

data want;

do _n_=1 by 1 until(last.sno);

set have nobs=nobs;

by sno;

if flag=1 then nobs=_n_;

if _n_>nobs then _f=1;

if _n_>1 and flag=10 then _f=1;

if _f ne 1 then output;

end;

drop _f;

run;

proc print;run;

Haikuo

Re: Logic help required.

Saravanan — Fri, 26 Oct 2012 17:20:29 GMT

Hi,

Thanks for the response. your code works perfectly.

Thanks a lot. Much appreciated.

Saravanan