topic Re: enumeration variable by group based on another variable in SAS Programming

enumeration variable by group based on another variable

knockchow — Sun, 25 Jun 2017 22:55:15 GMT

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!

Re: enumeration variable by group based on another variable

ChrisNZ — Sun, 25 Jun 2017 23:17:01 GMT

Like this?

data WANT;
  set HAVE;
  if COL1 then COUNT+COL1;
  else         COUNT=.;
run;

Re: enumeration variable by group based on another variable

knockchow — Mon, 26 Jun 2017 00:15:21 GMT

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;

Re: enumeration variable by group based on another variable

knockchow — Mon, 26 Jun 2017 01:40:57 GMT

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;

Re: enumeration variable by group based on another variable

ChrisNZ — Mon, 26 Jun 2017 02:04:27 GMT

Like this?

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

Re: enumeration variable by group based on another variable

ChrisNZ — Mon, 26 Jun 2017 02:11:52 GMT

Or another way:

data WANT;         
  set HAVE;
  retain COUNT;
  COUNT=ifn(missing(COL1) , .
       ,ifn(ID ne lag(ID) , 1
       ,                    sum(COUNT,1)));
run;

Re: enumeration variable by group based on another variable

knockchow — Mon, 26 Jun 2017 02:32:42 GMT

Thank you very much! I truly appreciate your help.