BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
knockchow
Calcite | Level 5

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! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

6 REPLIES 6
ChrisNZ
Tourmaline | Level 20

Like this?

data WANT;
  set HAVE;
  if COL1 then COUNT+COL1;
  else         COUNT=.;
run;
knockchow
Calcite | Level 5
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;
knockchow
Calcite | Level 5

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;

ChrisNZ
Tourmaline | Level 20

Like this?

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

Or another way:

data WANT;         
  set HAVE;
  retain COUNT;
  COUNT=ifn(missing(COL1) , .
       ,ifn(ID ne lag(ID) , 1
       ,                    sum(COUNT,1)));
run; 
knockchow
Calcite | Level 5
Thank you very much! I truly appreciate your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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