topic Re: How to select all columns but filter out on certain columns in SAS Programming

How to select all columns but filter out on certain columns

Crubal — Tue, 10 Oct 2017 21:01:15 GMT

Hi,

I have table_1 with columns (A, B, C, D, E).

For the rows within table_1, I would like to select rows of data where A, B, C are unique, but I also plan to keep information for D & E.

How can I do this?

Will this one work?

Proc Sql;

Create Table table_2 as select * from table_1

left join

(select distinct A, B, C from table_1) m

on table_1. A = m.A and table_1.B = m.B and table_1.C = m.C;

Quit;

Thank you!

Re: How to select all columns but filter out on certain columns

ballardw — Tue, 10 Oct 2017 21:29:04 GMT

You really should provide some example input data and the desired result.

"Unique combination" tends to imply a single result but when you say "plan to keep information for D & E." which values do you want?

if the example data looked like

A B C D E

Avalue Bvalue Cvalue somethingforD somethingforE

Avalue Bvalue Cvalue otherDvalue otherEvalue

Which values from D and E would you want?

Re: How to select all columns but filter out on certain columns

Crubal — Tue, 10 Oct 2017 22:18:21 GMT

Thanks for reminding.

For example,

Table_1:

A B C D E

1 2 2 4 5

1 2 3 6 7

1 2 3 8 2

In table_2: we only have first two rows. Since the third row has same (A, B, C) value with the second one.

Re: How to select all columns but filter out on certain columns

ballardw — Tue, 10 Oct 2017 23:20:42 GMT

If table_1 is sorted by A B C then this may do what you want:

data table_2;
   set table_1;
   by a b c;
   if first.c;
run;

If there is some actual criteria involved for choice of D and E you need to show it.

Re: How to select all columns but filter out on certain columns

Crubal — Wed, 11 Oct 2017 04:15:49 GMT

Hi @ballardw

Sorry I may not provide a good example.

Table_1, example as below:

A B C D E

1 2 2 4 5

1 2 3 6 7

2 1 4 5 8

1 2 3 8 2

Table_2, we will have the following:

A B C D E

1 2 2 4 5

1 2 3 6 7

2 1 4 5 8

Because the last row in table_1 is same as the third one because they have same information on (A, B and C)

I was wondering in your code,

will this part 'first.c' filter on 'A, B and C' information?

Thanks!

Re: How to select all columns but filter out on certain columns

Patrick — Wed, 11 Oct 2017 05:18:13 GMT

@Crubal

Why don't you use your sample data and run it with the code @ballardw provided?

In doing so: Is the result what you want? And if not how should the desired result using your sample data look like?

Re: How to select all columns but filter out on certain columns

ballardw — Wed, 11 Oct 2017 15:00:29 GMT

@Crubal wrote:

Hi @ballardw

Sorry I may not provide a good example.

Table_1, example as below:

A B C D E

1 2 2 4 5

1 2 3 6 7

2 1 4 5 8

1 2 3 8 2

Table_2, we will have the following:

A B C D E

1 2 2 4 5

1 2 3 6 7

2 1 4 5 8

Because the last row in table_1 is same as the third one because they have same information on (A, B and C)

I was wondering in your code,

will this part 'first.c' filter on 'A, B and C' information?

Thanks!

If the data is sorted by the variables A, B and C and you use a BY A B C statement in a data step when each record is processed the SAS adds automatic values that are true for the First and Last for each variable in combination on the By statement. Using "if first.C" checks if the current record has the first of a group of values for the variable C within the current combination of values of A and B and only keeps records where first.C is true.

Try sorting your data and running the example code and check to see if the result is as needed. If you don't like the resulting values of D and E you'll have to explain why not with a rule.

Re: How to select all columns but filter out on certain columns

mkeintz — Wed, 11 Oct 2017 15:25:13 GMT

If you sort the data by ABC, but tell proc sort to segregate the unique keys, you will solve this problem:

proc sort data=have out=_null_ nouniquekey  uniout=want;
  by a b c;
run;

Re: How to select all columns but filter out on certain columns

Crubal — Wed, 11 Oct 2017 16:48:17 GMT

Thanks!