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
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.
Please show what you expect as resulting dataset.
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 Response31 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
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
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.
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
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).
Hi Mkeintz,
It works.
Thank you much for your assistance on this.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.