Using first and last when counting (multiple variables).

Reply
Contributor
Posts: 30

Using first and last when counting (multiple variables).

Two variables, VAR1 and VAR2.

Sort OLD_TABLE by VAR1 VAR2.

data new_table;

set old_table;

by var1 var2;

   What is correct, here: A or B?

run;

A:

if first.var1 AND first.var2 then counter = 0;

counter + 1;

if last.var1 AND last.var2 then output;

B.

if first.var1 OR first.var2 then counter = 0;

counter + 1;

if last.var1 OR last.var2 then output;

Respected Advisor
Posts: 3,825

Re: Using first and last when counting (multiple variables).

What's the question?

Contributor
Posts: 30

Re: Using first and last when counting (multiple variables).

Which of A code or B code should be placed where the text "What is correct, here: A or B?" is placed.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Using first and last when counting (multiple variables).

Yes, I think what Patrick is asking, and also what I don't see, is to achieve what purpose.  Post some test data and required output, or run the code with both bits of code you have provided and see what the output is and compare to what you require.

Contributor
Posts: 30

Re: Using first and last when counting (multiple variables).

If OLD_TABLE was

VAR1_VAR2

1998_10

1998_10

1999_7

1999_10

2000_4

2000_4

2000_7

2000_7

2000_10

Then NEW_TABLE should become

VAR1_VAR2_COUNTER

1998_10_2

1999_7_1

1999_10_1

2000_4_2

2000_7_2

2000_10_1

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Using first and last when counting (multiple variables).

Ah, then your probably better off doing a count by in proc sql:

data have;

  length VAR1 VAR2 8;

  infile datalines dlm="_";

  input var1 var2;

datalines;

1998_10

1998_10

1999_7

1999_10

2000_4

2000_4

2000_7

2000_7

2000_10

;

run;

proc sql;

  create table WANT as

  select  distinct

          VAR1,

          VAR2,

          COUNT(*) as COUNTER

  from    HAVE

  group by VAR1,

           VAR2;

quit;

Contributor
Posts: 30

Re: Using first and last when counting (multiple variables).

Thank you RW9, but I don't want to use PROC SQL.

Trusted Advisor
Posts: 1,203

Re: Using first and last when counting (multiple variables).

data want;

set have;

by var1 var2;

if first.var1 or first.var2 then counter = 0;

counter + 1;

if last.var1 or last.var2 then output;

run;

Contributor
Posts: 30

Re: Using first and last when counting (multiple variables).

Thank you stat@sas.

So OR was the answer.

Super User
Super User
Posts: 6,317

Re: Using first and last when counting (multiple variables).

The answer is

C.

if first.var2 then counter = 0;

counter + 1;

if last.var2 then output;

When you have two BY variables they are nested.

So whenever LAST.VAR1 is true then LAST.VAR2 will also be true.  That is the last observation in the larger group is also the last observation in the last subgroup of that larger group.

Similarly for FIRST.VAR1 and FIRST.VAR2.

Grand Advisor
Posts: 9,571

Re: Using first and last when counting (multiple variables).

B also could work although there are some code unnecessary .

Ask a Question
Discussion stats
  • 10 replies
  • 318 views
  • 0 likes
  • 6 in conversation