DATA Step, Macro, Functions and more

enumeration variable by group based on another variable

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

enumeration variable by group based on another variable

Hello, 

 

This is the dataset that I have: 

ObsID_NAME_COL1
1100month1.
2100month21
3100month31
4100month41
5100month5.
6100month6.
7100month7.
8100month8.
9200month1.
10200month2 
11200month3 
12200month4 
13200month5.
14200month61
15200month71
16200month81

 

I would like to end up with this dataset: 

ObsID_NAME_COL1count
1100month1..
2100month211
3100month312
4100month413
5100month5..
6100month6..
7100month7..
8100month8..
9200month1..
10200month2 .
11200month3 .
12200month4 .
13200month511
14200month612
15200month713
16200month814

 

Thank you very much in advance! 

 


Accepted Solutions
Solution
‎06-25-2017 10:25 PM
PROC Star
Posts: 1,561

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;

View solution in original post


All Replies
PROC Star
Posts: 1,561

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:  

 

ObsID_NAME_COL1count
1100month1..
2100month211
3100month312
4100month413
5100month514
6100month61.5.
7100month71.6.
8100month81.7
9200month11.8
10200month2 19
11200month3 .
12200month4 .
13200month511
14200month612
15200month713
16200month814

 

However, I need it to look like this: 

ObsID_NAME_COL1count
1100month1..
2100month211
3100month312
4100month413
5100month514
6100month615
7100month71.6
8100month81.7
9200month11.1
10200month2 1.2
11200month3 .
12200month4 .
13200month511
14200month612
15200month713
16200month814

 

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
PROC Star
Posts: 1,561

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;
PROC Star
Posts: 1,561

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.

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

Discussion stats
  • 6 replies
  • 159 views
  • 1 like
  • 2 in conversation