topic Selecting group of data based on conditions in SAS Procedures

Selecting group of data based on conditions

WLP165 — Tue, 21 Oct 2014 20:45:50 GMT

I have the following groupings of data (fictitious data) and need a little help.

Value1 Value2 Value3 Value4 Value5 Value6

01 123 00 2 1234 1

01 123 01 3 1235 1

01 123 02 3 1234 2

02 124 00 2 1234 1

02 124 01 3 1236 1

02 124 02 3 1235 1

03 125 00 2 1230 1

03 125 01 3 1234 1

03 125 03 5 1235 1

The Value2 variable will always be the same within each group (account), such as shown above. Where the Value4 = 2 and Value5 = 1234, then I have to include ALL of the observations in each group (i.e., all three obs in the first two groups above) into an output SAS dataset. The last grouping of data would NOT be included.

Any help/examples are appreciated (have not yet done something like this in SAS; I'm a novice user).

Re: Selecting group of data based on conditions

Community_Help — Tue, 21 Oct 2014 20:58:51 GMT

WLP165, thanks for coming back to the community. I hope you find an answer to your question soon.

Re: Selecting group of data based on conditions

stat_sas — Tue, 21 Oct 2014 21:05:35 GMT

proc sql;

create table want as

select * from have

where value1 in (select value1 from have where value4=2 and value5=1234);

quit;

Re: Selecting group of data based on conditions

WLP165 — Tue, 21 Oct 2014 21:39:01 GMT

Thanks so much for the help!

Re: Selecting group of data based on conditions

WLP165 — Tue, 21 Oct 2014 23:36:30 GMT

I expanded the data example somewhat to be more realistic. See below. Given the same value4 and value5 extract criteria, the first group of obs for value1 = 01 would be pulled, the first group of obs where value1=02 would be pulled, and the second group of obs where value=03 would be pulled. Would this change your code solution any?

Value1 Value2 Value3 Value4 Value5 Value6

01 123 00 2 1234 1

01 123 01 3 1235 1

01 123 02 3 1234 2

01 127 00 2 1237 1

01 127 01 3 1234 1

01 127 02 3 1234 1

02 124 00 2 1234 1

02 124 01 3 1236 1

02 124 02 3 1235 1

02 128 00 2 1238 1

02 128 01 3 1234 1

02 128 02 3 1234 1

03 125 00 2 1230 1

03 125 01 3 1234 1

03 125 03 5 1235 1

03 129 00 2 1234 1

03 129 01 3 1234 1

03 129 02 3 1234 1

Re: Selecting group of data based on conditions

art297 — Wed, 22 Oct 2014 00:56:42 GMT

If I correctly understand what you want to do, one minor correction needed to be made for stat's proposed code to work, namely:

proc sql;

create table want as

select * from have

where value2 in (select value2 from have where value4=2 and value5=1234);

quit;

Re: Selecting group of data based on conditions

Ksharp — Wed, 22 Oct 2014 13:08:21 GMT

data have;
input (Value1   Value2   Value3   Value4   Value5   Value6 ) ($);
cards;
01          123         00          2            1234       1
01          123         01          3            1235       1
01          123         02          3            1234       2
01          127         00          2            1237       1
01          127         01          3            1234       1
01          127         02          3            1234       1
02          124         00          2            1234       1
02          124         01          3            1236       1
02          124         02          3            1235       1
02          128         00          2            1238       1
02          128         01          3            1234       1
02          128         02          3            1234       1
03          125         00          2            1230       1
03          125         01          3            1234       1
03          125         03          5            1235       1
03          129         00          2            1234       1
03          129         01          3            1234       1
03          129         02          3            1234       1
;
run;
proc sql;
create table want as
 select * from have group by value1,value2 having sum(catx(' ',value4,value5)='2 1234') gt 0;
quit;

Xia Keshan

Re: Selecting group of data based on conditions

WLP165 — Wed, 22 Oct 2014 15:08:40 GMT

I tried Xia's code. I do get a SAS dataset created, but with no observations and six columns. Also, the SAS Log shows a message that says "The query requires remerging summary statistics back with the original data."

Re: Selecting group of data based on conditions

WLP165 — Wed, 22 Oct 2014 15:11:21 GMT

One additional note. I bring in the data from an external text file via a data step using infile and input statements. However, the suggested Proc SQL code used was from Xia.

William

Re: Selecting group of data based on conditions

art297 — Wed, 22 Oct 2014 15:26:30 GMT

Are Value4 and Value5 both character variables? Also, are both Value1 and Value2 needed to identity a unique group, or just Value2?

The remerging summary statistics note is simply a note produced when incorporating summary functions. Nothing to be concerned about. But not getting the desired data is definitely a concern.

Re: Selecting group of data based on conditions

WLP165 — Wed, 22 Oct 2014 15:57:09 GMT

The Value4 and Value5 are both character variables. Also, yes, both Value1 and Value2 would be needed to identify unique groups. The Value1 is basically a fictitious code (for, let's say, a State), and Value2 is simply a unique fictitious number identifier of each group within each Value1. In other words, there will be many unique Value2's within each Value1.

Re: Selecting group of data based on conditions

art297 — Wed, 22 Oct 2014 16:03:22 GMT

Actually, 's code will work with both character and numeric. I'll have to guess that your actual data doesn't have any records where Value4=2 and Value5=1234

Re: Selecting group of data based on conditions

WLP165 — Wed, 22 Oct 2014 16:47:26 GMT

My actual data does have Value4 of 2s, which are also inputted as character format. The Value5 values (also inputted as character) are actually six-digit numbers (just did them as four-digits here for illustrative purposes). Also, there is no summing done in the mathematical sense. Just trying to extract certain observations (records) from a database based on the criteria noted, and then output the records to a separate dataset.

William

Re: Selecting group of data based on conditions

art297 — Wed, 22 Oct 2014 17:20:31 GMT

's code is simply counting the number of times that value4 and value5 contain a 2 and 1234, respectively, within the groups records. If the combination exists at least once, all of the groups records are output. You now said that value5 is actually a 6 character variable, thus obviously 1234 won't match. I think you have to provide a better example of what your data look like and what your criteria are. His code can handle any length variable.

Re: Selecting group of data based on conditions

WLP165 — Wed, 22 Oct 2014 17:49:03 GMT

My apologies, I inadvertently used an incorrect Value5 in my extract criteria. The Proc SQL code worked and got what I needed. Thanks so much for your help!

William

Re: Selecting group of data based on conditions

WLP165 — Fri, 24 Oct 2014 16:55:57 GMT

Using the above 18-line data example, I am currently trying to do a slightly different data extract. This is a little more involved but Value1 and Value2 are still used to identify each grouping and all variables are still character. The new extract criteria is: if Value4 is equal to 3 or 5, and Value5 is equal to 1234, and Value6 is equal to 1, and if Value4 = 2 and Value5 is NOT equal to 1234, then write that group to the output SAS dataset.

I can't figure out how to handle/incorporate the latter condition (i.e., "and if Value4 = 2 and Value5 is NOT equal to 1234") with the set of conditions before it.

William

Re: Selecting group of data based on conditions

WLP165 — Fri, 24 Oct 2014 18:17:12 GMT

Below is the Proc SQL code I was trying to use for the above conditions. I keep getting an empty output dataset.

proc sql;

create table want as

select * from have

group by value1,value2 having

sum (catx (' ',value4,value5,value6) = '3 1234 1') and

sum (catx (' ',value4,value5,value6) = '5 1234 1') and

sum (catx (' ',value4) = '2' and sum (catx (' ',value5) <> '1234') gt 0;

quit;

Re: Selecting group of data based on conditions

art297 — Fri, 24 Oct 2014 19:13:29 GMT

Is the following what you want to use?:

proc sql;

create table want as

select * from have

group by value1,value2 having

sum (catx (' ',value4,value5,value6) = '3 1234 1') or

sum (catx (' ',value4,value5,value6) = '5 1234 1') or

(sum (catt (value4) = '2') and sum (catt (value5) <> '1234'))

;

quit;

Re: Selecting group of data based on conditions

WLP165 — Mon, 27 Oct 2014 14:44:38 GMT

Hello,

I tried your suggested Proc SQL code above, but I get all 18 observations written to the output dataset. Given the newer extract criteria, would expect the groups of observations to be excluded from the output dataset would be:

- first grouping of data (first three obs) under Value1 = 01 due to the first observation in the group having Value4 = 2 and Value5 = 1234, and the third observation having Value6 = 2), and

- first grouping of data (first three obs) under Value1 = 02 due to the first observation in the group having Value5 = 1234, and the second and third observations having Value5 = 1236 and 1235, respectively, and

- second grouping of data (second three obs) under Value1 = 03 due to the first observation in the group having Value5 = 1234

The other three groups should make it into the output dataset. I guess this extract is a little trickier.

William

Re: Selecting group of data based on conditions

art297 — Mon, 27 Oct 2014 15:40:32 GMT

Still not sure if I correctly understand your selection rules. Is the following what you are trying to accomplish?:

proc sql;

create table want as

select * from have

group by value1,value2 having

(sum (catx (' ',value4,value5,value6) = '3 1234 1') or

sum (catx (' ',value4,value5,value6) = '5 1234 1') ) and

sum (catx (' ',value4,value5) = '2 1234') eq 0

;

quit;