DATA Step, Macro, Functions and more

RE: counting by two grouping variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

RE: counting by two grouping variables

Hi....I am trying to do a count by two grouping variables and would like it to rest to a count of 1 when a new "Numb" occurs and not after a new "New_Dos" occurs. I am using a Data Step that generates the following output ( highlighted the counts =1 and should be 2):

 

DATA NEW;

SET NEW;

BY NUMB NEW_DOS;

COUNT=1;

IF NUMB = LAG(NUMB) AND NEW_DOS ^= LAG(NEW_DOS) THEN DO;

     COUNT=COUNT + 1;

     END;

RUN;

 

NUMB NEW_DOS COUNT
00147680 ML 1
00147680 ML 1
00147680 ML 1
00147680 W / V 2
00306819 W / V 1
00306819 W / V 1
00306819 W / W 2
00461024 % 1
00461024 ML 2
00532266 12 ML 1
00532266 ML 2
00773530 10 ML 1
00773530 10 ML 1
00773530 10 ML 1
00773530 10 ML 1
00773530 TAB 2
00773530 TAB 1
00773530 TAB 1
00816132 G 1
00816132 W / W 2
01914537 ML 1

 

Thanks.


Accepted Solutions
Solution
‎10-09-2015 11:37 AM
Super User
Super User
Posts: 7,413

Re: RE: counting by two grouping variables

Ah, pop a retain in then:

data new;
  set new;
  by numb new_dos;
  retain count;
  if first.num then count=1;
  if numb=lag(numb) and new_dos ne lag(new_dos) then do;
    count=count+1;
  end;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

Re: RE: counting by two grouping variables

To reset your variable on numb, then just do:

data new;
  set new;
  by numb new_dos;
  if first.num then count=1;
  if numb=lag(numb) and new_dos ne lag(new_dos) then do;
    count=count+1;
  end;
run;

Do note the updates I have made to the code, not using all uppercase, and using ne rather than ^=.

Trusted Advisor
Posts: 1,624

Re: RE: counting by two grouping variables

Why is ne preferred to ^= ???

Super User
Super User
Posts: 7,413

Re: RE: counting by two grouping variables

Can you think of any occurence where "ne" could be misinterpreted?  ^ is power in most maths, and can be an escape char etc.  Personally I find the ne to be more readable, and that is my principal aim in programming.

Trusted Advisor
Posts: 1,624

Re: RE: counting by two grouping variables


RW9 wrote:

Can you think of any occurence where "ne" could be misinterpreted?

No, I can't. Please enlighten me.

Super User
Super User
Posts: 7,413

Re: RE: counting by two grouping variables

Thats my point, ne reads easier, and isn't used for anything else.

Trusted Advisor
Posts: 1,624

Re: RE: counting by two grouping variables


RW9 wrote:

ne reads easier and isn't used for anything else.


My opinion is different. I have no problems seeing ^= in code and interpreting it or judging it to be readable.

Super User
Posts: 10,526

Re: RE: counting by two grouping variables

Not to thread hijack but typos like =^ do not cause an error whereas using "en" instead of "ne" will:


data _null_;
input x y;
if x =^ y then put "is this expected for " x= y=;
datalines;
0 0
0 1
3 5
;
run;
Regular Contributor
Posts: 222

Re: RE: counting by two grouping variables

Hi RW9....Thanks for your help. I tried it and I get a 1 for the first Numb ( as from the first If Statement) and all the rest are empty blanks...

Solution
‎10-09-2015 11:37 AM
Super User
Super User
Posts: 7,413

Re: RE: counting by two grouping variables

Ah, pop a retain in then:

data new;
  set new;
  by numb new_dos;
  retain count;
  if first.num then count=1;
  if numb=lag(numb) and new_dos ne lag(new_dos) then do;
    count=count+1;
  end;
run;
Valued Guide
Posts: 765

Re: RE: counting by two grouping variables

[ Edited ]

Hi, this also works (lot less SAS code) ...

 

data y;
set x;
by num newdos;
newcount + first.newdos - first.num*newcount;
run;

 

take a look at ... Tips:Between and Within Group Counters

http://www.sascommunity.org/wiki/Tips:Between_and_Within_Group_Counters

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 367 views
  • 0 likes
  • 5 in conversation