topic Re: Count based on group variables in SAS Programming

Count based on group variables

sayanapex06 — Mon, 11 Dec 2017 17:41:00 GMT

This is my Data :

DATA TEST ;
LENGTH A $3. B E $6. C $3. ;
FORMAT D 12.4 ;
INFILE DATALINES DLM='~' ;
INPUT A B C D E ;
DATALINES ;
ABC~ITEMS3~USD~10~1234A1
DBC~ITEMS3~USD~20~1234A2
VBC~ITEMS3~USD~15~1234A3
BBC~ITEMS3~USD~10~1234A4
NBC~ITEMS3~ILS~04~1234A5
KBC~ITEMS3~ILS~09~1234A6
PBC~ITEMS6~USD~09~1234B1
VBC~ITEMS6~USD~09~1234B2
CBC~ITEMS6~EUR~09~1234B3
TBC~ITEMS6~EUR~09~1234B4

Now I want the output to be counted based on variable C and keeping in mind from the common B.

The output I want is :

B C E seq_num

IITEMS3 USD 1234A1 1
ITEMS3 USD 1234A2 1
ITEMS3 USD 1234A3 1
ITEMS3 USD 1234A4 1
ITEMS3 ILS 1234A5 2
ITEMS3 ILS 1234A6 2
ITEMS6 USD 1234B1 1
ITEMS6 USD 1234B2 1
ITEMS6 EUR 1234B3 2
ITEMS6 EUR 1234B4 2

The seq_num as you can see is based on the count of variable C, I want it to be same if its repeating and change only ( by 1) only when a new value comes in the variable C. Note the father variable being B.

I am confused with how to use the FIRST. LAST. to get this output.

Please could you help me with this.

P.S. I need it urgently..

Thank you

Re: Count based on group variables

sayanapex06 — Mon, 11 Dec 2017 18:13:06 GMT

@data_null__ need your help on this one...

Re: Count based on group variables

Reeza — Mon, 11 Dec 2017 18:18:23 GMT

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

This tutorial will walk you through how to create your groups. If you have difficulties, please post the code you've attempted.

Re: Count based on group variables

sayanapex06 — Mon, 11 Dec 2017 18:38:52 GMT

I tried but couldnt get the result.

Below is my code :

PROC SORT DATA = TEST ; BY B C E ;
DATA XXX ;
SEQ = 0 ;
SET TEST ;
BY B C ;

IF FIRST.B
THEN DO ;
IF FIRST.C THEN SEQ = 1 ;
END;
IF LAST.C THEN
SEQ = SEQ + 1 ;
RUN ;

PROC PRINT DATA = XXX ;
VAR C B SEQ ;

Output :

Obs C B SEQ

1 ILS ITEMS3 1
2 ILS ITEMS3 1
3 USD ITEMS3 0
4 USD ITEMS3 0
5 USD ITEMS3 0
6 USD ITEMS3 1
7 EUR ITEMS6 1
8 EUR ITEMS6 1
9 USD ITEMS6 0
10 USD ITEMS6 1

whereas I want it to be :

Obs C B SEQ

1 ILS ITEMS3 1
2 ILS ITEMS3 1
3 USD ITEMS3 2
4 USD ITEMS3 2
5 USD ITEMS3 2
6 USD ITEMS3 2
7 EUR ITEMS6 1
8 EUR ITEMS6 1
9 USD ITEMS6 2
10 USD ITEMS6 2

Please help me, its urgent

Re: Count based on group variables

novinosrin — Mon, 11 Dec 2017 18:56:12 GMT

Don't panic. Is this homework lol ? Reeza is helping you learn on your own by self learning and researching, which is only possible when you try. Anyways:

DATA have ;
LENGTH A $3. B E $6. C $3. ;
FORMAT D 12.4 ;
INFILE DATALINES DLM='~' ;
INPUT A B C D E ;
DATALINES ;
ABC~ITEMS3~USD~10~1234A1
DBC~ITEMS3~USD~20~1234A2
VBC~ITEMS3~USD~15~1234A3
BBC~ITEMS3~USD~10~1234A4
NBC~ITEMS3~ILS~04~1234A5
KBC~ITEMS3~ILS~09~1234A6
PBC~ITEMS6~USD~09~1234B1
VBC~ITEMS6~USD~09~1234B2
CBC~ITEMS6~EUR~09~1234B3
TBC~ITEMS6~EUR~09~1234B4
;

proc sort data=have ;
by b c;
run;

data want;
set have;
by b c;
if first.b then call missing(seq_num);
if first.c then seq_num+1;
run;

Re: Count based on group variables

Astounding — Mon, 11 Dec 2017 18:56:50 GMT

You're fiddling with the right tools. Here's a way that should work:

DATA XXX ;
SET TEST ;
BY B C ;

IF FIRST.B THEN SEQ = 1 ;
ELSE IF FIRST.C THEN SEQ + 1 ;
RUN ;

Re: Count based on group variables

sayanapex06 — Mon, 11 Dec 2017 19:24:49 GMT

Thanks a ton.. But could you help me understand what is the :

call missing function doing.

Re: Count based on group variables

novinosrin — Mon, 11 Dec 2017 19:27:33 GMT

Call missing here assigns a missing value in order to override the self retain of the sum statement not to increment when by groups change.

Re: Count based on group variables

sayanapex06 — Mon, 11 Dec 2017 21:05:06 GMT

Thanks