topic Retaining common observations within a data set following different criteria. in SAS Programming

Retaining common observations within a data set following different criteria.

FerGui1 — Mon, 21 Jun 2021 22:14:12 GMT

Hello,

From the data set below (data set A) I need to retain (or extract to a subdata set) common Condition values between the Period 1 and 2 (which would be B, C, D, and E); I also need to do the same among Period 1, 2 and 3 (C, D, E); and among Period 1, 2, 3, and 4 (D, E). Any help on this will be much appreciated.

Thanks.

Data set A
Period Condition Response1 Response2 Response3

1 A

1 B

1 C

1 D

1 E

2 B

2 C

2 D

2 E

3 C

3 D

3 E

4 D

4 E

Re: Retaining common observations within a data set following different criteria.

ballardw — Mon, 21 Jun 2021 22:29:41 GMT

You are not expressing your need very clearly.

Show us least two different of these subsets.

And why do you need a different data set? If the criteria is easy enough you could likely use that for any need.

Re: Retaining common observations within a data set following different criteria.

Kurt_Bremser — Mon, 21 Jun 2021 22:30:18 GMT

Please show what you expect as resulting dataset.

Re: Retaining common observations within a data set following different criteria.

Reeza — Mon, 21 Jun 2021 22:35:12 GMT

Could you please illustrate the expected output, if this is your input data?

Are your rules stated below fixed, or do they change depending on the data somehow?

@FerGui1 wrote:

Hello,

From the data set below (data set A) I need to retain (or extract to a subdata set) common Condition values between the Period 1 and 2 (which would be B, C, D, and E); I also need to do the same among Period 1, 2 and 3 (C, D, E); and among Period 1, 2, 3, and 4 (D, E). Any help on this will be much appreciated.

Thanks.

Data set A
Period      Condition    Response1    Response2    Response3

1              A

1              B

1              C

1              D

1              E

2              B

2              C

2              D

2              E

3              C

3              D

3              E

4              D

4              E

Re: Retaining common observations within a data set following different criteria.

tarheel13 — Mon, 21 Jun 2021 22:53:48 GMT

Why don’t you just make a dataset for period 1 and another for period 2 and then merge by condition. You can use in operators (in=a) and (in=b) and do if a and b.

Re: Retaining common observations within a data set following different criteria.

mkeintz — Tue, 22 Jun 2021 04:32:29 GMT

It's unclear, but I think this is what you want:

data have;
  input Period Condition :$1. ;
datalines;
1 A
1 B
1 C
1 D
1 E
2 B
2 C
2 D
2 E
3 C
3 D
3 E
4 D
4 E
run;
data want (drop=period);
  merge have (where=(period=1) in=in1)
        have (where=(period=2) in=in2)
        have (where=(period=3) in=in3)
        have (where=(period=4) in=in4);
  by condition;
  period_1_2 = min(in1,in2);
  period_1_2_3 = min(in1,in2,in3);
  period_1_2_3_4 = min(in1,in2,in3,in4);
run;

This assumes that within each period, the data are sorted by condition, although the data need not be sorted by period.

Re: Retaining common observations within a data set following different criteria.

Ksharp — Tue, 22 Jun 2021 12:18:28 GMT

data have;
  input Period Condition :$1. ;
datalines;
1 A
1 B
1 C
1 D
1 E
2 B
2 C
2 D
2 E
3 C
3 D
3 E
4 D
4 E
;
run;

proc sql;
create table period_1_2 as
select Condition from have where period=1
intersect
select Condition from have where period=2
;


create table period_1_2_3 as
select Condition from period_1_2
intersect
select Condition from have where period=3
;

quit;

Re: Retaining common observations within a data set following different criteria.

FerGui1 — Tue, 22 Jun 2021 13:48:53 GMT

Hi MKeintz,

My apologies if I was not clear with my question. To be more clear, the major data set is:

Data set A

Period Batch Condition Response1 Response2 Response3

1 1 A

1 1 B

1 1 C

1 1 D

1 1 E

1 2 A

1 2 B

1 2 C

1 2 D

1 2 E

2 1 B

2 1 C

2 1 D

2 1 E

2 2 B

2 2 C

2 2 D

2 2 E

3 1 C

3 1 D

3 1 E

3 2 C

3 2 D

3 2 E

4 1 D

4 1 E

4 2 D

4 2 E

The Batch variable is a block effect in the experiment (replication).

From this data set I need to create 3 sub data sets for further analyses:

The first one (sub data set A1) should only include common observations within the variable Condition for Period 1 and Period 2, s this sub data set will look like:

Sub data set A1

Period Batch Condition Response1 Response2 Response3

1 1 B

1 1 C

1 1 D

1 1 E

1 2 B

1 2 C

1 2 D

1 2 E

2 1 B

2 1 C

2 1 D

2 1 E

2 2 B

2 2 C

2 2 D

2 2 E

The second one (sub data set A2) should only include common observations within the variable Condition for Period 1, Period 2, and Period 3, so this sub data set will look like:

Sub data set A2

Period Batch Condition Response1 Response2 Response3

1 1 C

1 1 D

1 1 E

1 2 C

1 2 D

1 2 E

2 1 C

2 1 D

2 1 E

2 2 C

2 2 D

2 2 E

3 1 C

3 1 D

3 1 E

3 2 C

3 2 D

3 2 E

And the third sub data set will include all periods, so it will look like:

Sub data set A3

Period Batch Condition Response1 Response2 Response3

1 1 D

1 1 E

1 2 D

1 2 E

2 1 D

2 1 E

2 2 D

2 2 E

3 1 D

3 1 E

3 2 D

3 2 E

4 1 D

4 1 E

4 2 D

4 2 E

Re: Retaining common observations within a data set following different criteria.

FerGui1 — Tue, 22 Jun 2021 13:50:39 GMT

I did that but the only output I've got was a column with the condition values that were common among periods, nothing else.

Re: Retaining common observations within a data set following different criteria.

FerGui1 — Tue, 22 Jun 2021 13:51:54 GMT

Hi Reeza,

The major data set is:

Data set A

Period Batch Condition Response1 Response2 Response3

1 1 A

1 1 B

1 1 C

1 1 D

1 1 E

1 2 A

1 2 B

1 2 C

1 2 D

1 2 E

2 1 B

2 1 C

2 1 D

2 1 E

2 2 B

2 2 C

2 2 D

2 2 E

3 1 C

3 1 D

3 1 E

3 2 C

3 2 D

3 2 E

4 1 D

4 1 E

4 2 D

4 2 E

The Batch variable is a block effect in the experiment (replication).

From this data set I need to create 3 sub data sets for further analyses:

The first one (sub data set A1) should only include common observations within the variable Condition for Period 1 and Period 2, s this sub data set will look like:

Sub data set A1

Period Batch Condition Response1 Response2 Response3

1 1 B

1 1 C

1 1 D

1 1 E

1 2 B

1 2 C

1 2 D

1 2 E

2 1 B

2 1 C

2 1 D

2 1 E

2 2 B

2 2 C

2 2 D

2 2 E

The second one (sub data set A2) should only include common observations within the variable Condition for Period 1, Period 2, and Period 3, so this sub data set will look like:

Sub data set A2

Period Batch Condition Response1 Response2 Response3

1 1 C

1 1 D

1 1 E

1 2 C

1 2 D

1 2 E

2 1 C

2 1 D

2 1 E

2 2 C

2 2 D

2 2 E

3 1 C

3 1 D

3 1 E

3 2 C

3 2 D

3 2 E

And the third sub data set will include all periods, so it will look like:

Sub data set A3

Period Batch Condition Response1 Response2 Response3

1 1 D

1 1 E

1 2 D

1 2 E

2 1 D

2 1 E

2 2 D

2 2 E

3 1 D

3 1 E

3 2 D

3 2 E

4 1 D

4 1 E

4 2 D

4 2 E

Re: Retaining common observations within a data set following different criteria.

tarheel13 — Tue, 22 Jun 2021 13:55:07 GMT

Can you please post the data as data lines? It is hard to see what columns you have like this.

Re: Retaining common observations within a data set following different criteria.

mkeintz — Tue, 22 Jun 2021 20:42:31 GMT

Consider:

data period_12   (where=(_max_period>=2 and period<=2))
     period_123  (where=(_max_period>=3 and period<=3))
     period_1234 (where=(_max_period>=4 and period<=4));

  merge have (where=(period=1) in=in1)
        have (where=(period=2) in=in2) 
        have (where=(period=3) in=in3) 
        have (where=(period=4) in=in4) ;
  by batch condition;

  if min(in1,in2,in3,in4) then _max_period=4; else
  if min(in1,in2,in3)     then _max_period=3; else
  if min(in1,in2)         then _max_period=2;

  if _max_period^=. then do period=1 to _max_period;
    output;
  end;
run;

The OUTPUT statements write to all 3 output datasets. But those datasets each have their own filter to ensure that only the right obs are written (i.e. so that period_1234 doesn't get any obs that are common only to 1 and 2).

Re: Retaining common observations within a data set following different criteria.

FerGui1 — Wed, 23 Jun 2021 21:06:57 GMT

Hi Mkeintz,

It works.

Thank you much for your assistance on this.