topic Re: Select groupings if contains an intermittent field in SAS Procedures

Select groupings if contains an intermittent field

podarum — Thu, 17 Oct 2013 19:38:06 GMT

I have a large dataset grouped by Field_A and dates for each rows within the groups. There is also another Field_B that may or may not be intermittent within each group.. I need to pick only the groupings that have Field_B intermittent.. here's what I mean:

HAVE:

Field_A Date Field_B
A 2001.01 T

A 2001.02 C

A 2001.03 C

A 2001.04 T

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

B 2001.01 T

B 2001.02 T

B 2001.03 C

B 2001.04 C

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

C 2001.01 C

C 2001.02 T

C 2001.03 C

C 2001.04 T

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

D 2001.01 C

D 2001.02 C

D 2001.03 T

D 2001.04 T

WANT:

Field_A Date Field_B
A 2001.01 T

A 2001.02 C

A 2001.03 C

A 2001.04 T

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

C 2001.01 T

C 2001.02 C

C 2001.03 T

C 2001.04 C

WANT tables shows that Field_B has values that are mixed within it, not arranged as seen in Field_A = B or D in the HAVE table.

Re: Select groupings if contains an intermittent field

art297 — Thu, 17 Oct 2013 19:50:29 GMT

I think you have to specify exactly what you mean by "intermittent".

Re: Select groupings if contains an intermittent field

podarum — Thu, 17 Oct 2013 20:10:09 GMT

The options could be either Option 1 : Field_B is 1 value and then the other (eg. T's and then C's or vice versa) OR

Option 2 : T's then C's and then T's again or C's then T's and then C's again... so if there is only one switch in Feild_B then don't keep . if there is more than one switch in Field_B then keep (sorted by date and groupped by Field_A)

Re: Select groupings if contains an intermittent field

Reeza — Thu, 17 Oct 2013 20:29:24 GMT

Here's a very basic way. Count the number of switches in a dataset and then select the ones that meet your criteria.

data have;
input Field_A $        Date   $       Field_B $;
cards;
A                 2001.01             T
A                 2001.02             C
A                 2001.03             C
A                 2001.04             T
B                 2001.01             T
B                 2001.02             T
B                 2001.03             C
B                 2001.04             C
C                 2001.01             C
C                 2001.02             T
C                 2001.03             C
C                 2001.04             T
D                 2001.01             C
D                 2001.02             C
D                 2001.03             T
D                 2001.04             T
;
run;

data have2;
set have;
by field_a field_b notsorted;

retain switch;

if first.field_a then switch=0;

if first.field_b and not first.field_a then switch+1;

run;

proc sql;
create table want as
select * from have2
group by field_a
having max(switch)>1;
quit;

Re: Select groupings if contains an intermittent field

art297 — Thu, 17 Oct 2013 20:30:18 GMT

: As usual, you probably have a number of options. The following probably isn't the most efficient, but easy to follow what the code is doing:

data want (drop=test1-test3);

set have (in=a) have (in=b);;

by field_a;

retain test1-test3;

length test1 test2 $1;

if a then do;

if first.field_a then do;

test1=field_b;

call missing(test2);

call missing(test3);

end;

else if missing(test2) then do;

if field_b ne test1 then test2=field_b;

end;

else if field_b ne test2 then test3=1;

end;

else if test3 then output;

run;

Re: Select groupings if contains an intermittent field

PGStats — Fri, 18 Oct 2013 01:18:38 GMT

I would count on the BY mechanism to count the number of switches in Field_B, like this :

data have;
input Field_A $ Date $ Field_B $;
datalines;
A                 2001.01             T
A                 2001.02             C
A                 2001.03             C
A                 2001.04             T
B                 2001.01             T
B                 2001.02             T
B                 2001.03             C
B                 2001.04             C
C                 2001.01             C
C                 2001.02             T
C                 2001.03             C
C                 2001.04             T
D                 2001.01             C
D                 2001.02             C
D                 2001.03             T
D                 2001.04             T
;

data want;
do until(last.Field_A);
     set have; by Field_A Field_B notsorted;
     c = sum(c, first.Field_b);
     end;
do until(last.Field_A);
     set have; by Field_A Field_B notsorted;
     if c <= 2 then output;
     end;
drop c;
run;

proc print data=want noobs; run;

Re: Select groupings if contains an intermittent field

Haikuo — Fri, 18 Oct 2013 01:37:41 GMT

Liked!