topic Re: RE: counting by two grouping variables in SAS Programming

RE: counting by two grouping variables

twildone — Fri, 09 Oct 2015 13:55:31 GMT

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.

Re: RE: counting by two grouping variables

RW9 — Fri, 09 Oct 2015 14:25:55 GMT

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 ^=.

Re: RE: counting by two grouping variables

PaigeMiller — Fri, 09 Oct 2015 14:39:37 GMT

Why is ne preferred to ^= ???

Re: RE: counting by two grouping variables

twildone — Fri, 09 Oct 2015 14:43:50 GMT

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...

Re: RE: counting by two grouping variables

RW9 — Fri, 09 Oct 2015 15:00:02 GMT

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;

Re: RE: counting by two grouping variables

RW9 — Fri, 09 Oct 2015 15:02:15 GMT

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.

Re: RE: counting by two grouping variables

PaigeMiller — Fri, 09 Oct 2015 15:07:19 GMT

@RW9 wrote:
Can you think of any occurence where "ne" could be misinterpreted?

No, I can't. Please enlighten me.

Re: RE: counting by two grouping variables

RW9 — Fri, 09 Oct 2015 15:21:27 GMT

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

Re: RE: counting by two grouping variables

PaigeMiller — Fri, 09 Oct 2015 16:15:10 GMT

@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.

Re: RE: counting by two grouping variables

ballardw — Fri, 09 Oct 2015 16:25:22 GMT

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;

Re: RE: counting by two grouping variables

MikeZdeb — Sat, 10 Oct 2015 21:43:57 GMT

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