Solved
New Contributor
Posts: 4

# enumeration variable by group based on another variable

Hello,

This is the dataset that I have:

 Obs ID _NAME_ COL1 1 100 month1 . 2 100 month2 1 3 100 month3 1 4 100 month4 1 5 100 month5 . 6 100 month6 . 7 100 month7 . 8 100 month8 . 9 200 month1 . 10 200 month2 11 200 month3 12 200 month4 13 200 month5 . 14 200 month6 1 15 200 month7 1 16 200 month8 1

I would like to end up with this dataset:

 Obs ID _NAME_ COL1 count 1 100 month1 . . 2 100 month2 1 1 3 100 month3 1 2 4 100 month4 1 3 5 100 month5 . . 6 100 month6 . . 7 100 month7 . . 8 100 month8 . . 9 200 month1 . . 10 200 month2 . 11 200 month3 . 12 200 month4 . 13 200 month5 1 1 14 200 month6 1 2 15 200 month7 1 3 16 200 month8 1 4

Thank you very much in advance!

Accepted Solutions
Solution
‎06-25-2017 10:25 PM
Super User
Posts: 2,516

## Re: enumeration variable by group based on another variable

Like this?

``````data WANT;
set HAVE;
if ID ne lag(ID) then COUNT=0;
if COL1 = 1 then COUNT+COL1;
else             COUNT=.;
run;``````

All Replies
Super User
Posts: 2,516

## Re: enumeration variable by group based on another variable

Like this?

``````data WANT;
set HAVE;
if COL1 then COUNT+COL1;
else         COUNT=.;
run;``````
New Contributor
Posts: 4

## Re: enumeration variable by group based on another variable

Yes! Thank you so much for leading me to the correct answer.

One small thing I would change is:

data WANT;
set HAVE;
if COL1 = 1 then COUNT+COL1;
else COUNT=.;
run;
New Contributor
Posts: 4

## Re: enumeration variable by group based on another variable

Hi @ChrisNZ,

I'm afraid that I might have spoken too soon. In my data, I have instances such as so when I use the coding that you suggested to me:

 Obs ID _NAME_ COL1 count 1 100 month1 . . 2 100 month2 1 1 3 100 month3 1 2 4 100 month4 1 3 5 100 month5 1 4 6 100 month6 1. 5. 7 100 month7 1. 6. 8 100 month8 1. 7 9 200 month1 1. 8 10 200 month2 1 9 11 200 month3 . 12 200 month4 . 13 200 month5 1 1 14 200 month6 1 2 15 200 month7 1 3 16 200 month8 1 4

However, I need it to look like this:

 Obs ID _NAME_ COL1 count 1 100 month1 . . 2 100 month2 1 1 3 100 month3 1 2 4 100 month4 1 3 5 100 month5 1 4 6 100 month6 1 5 7 100 month7 1 .6 8 100 month8 1 .7 9 200 month1 1 .1 10 200 month2 1 .2 11 200 month3 . 12 200 month4 . 13 200 month5 1 1 14 200 month6 1 2 15 200 month7 1 3 16 200 month8 1 4

I would like to have it numbering restarted once the id changes. I have tried this code but it does not work: Would you have any additional suggestion for me? Thank you.

data want;
set have;
by id;
if COL1 = 1 then count+col;
else col1 = .;
run;

Solution
‎06-25-2017 10:25 PM
Super User
Posts: 2,516

## Re: enumeration variable by group based on another variable

Like this?

``````data WANT;
set HAVE;
if ID ne lag(ID) then COUNT=0;
if COL1 = 1 then COUNT+COL1;
else             COUNT=.;
run;``````
Super User
Posts: 2,516

## Re: enumeration variable by group based on another variable

Or another way:

``````data WANT;
set HAVE;
retain COUNT;
COUNT=ifn(missing(COL1) , .
,ifn(ID ne lag(ID) , 1
,                    sum(COUNT,1)));
run; ``````
New Contributor
Posts: 4

## Re: enumeration variable by group based on another variable

Thank you very much! I truly appreciate your help.
🔒 This topic is solved and locked.