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

Hello

 

I'm trying to assign a value (incremental by 1) to each row of a dataset, resetting to 1 when it's a new group.

 

(in the example I provide, column count should show 1,2,3 for the first three rows, then restarting from 1 on row 4 etc..)

 

it seems so simple but I can't get it work!

 

thanks 

 

 

data WORKING_DAYS;
     input DAY MTH;
     datalines;
20190102 201901
20190103 201901
20190104 201901
20190201 201902
20190204 201902
20190205 201902
20190301 201903
20190304 201903
20190305 201903
20190401 201904
20190402 201904
20190403 201904
;
run;
 
data WORK.WORKING_DAYS;
     set WORK.WORKING_DAYS;
     by DAY;
 
     if first.MTH then
           count=1;
else count = count+1;
 
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You have to include the variables in the BY statement if you want SAS to set values for FIRST. and LAST. variables for them.

by DAY MTH;

You have to tell SAS not to reset the new variable COUNT to missing when it starts the next iteration.  You can use a RETAIN statement for that. 

retain count;

Or you could modify your code to use a SUM statement, which will automatically retain the variable that you are using as the accumulator in the SUM statement.

if first.MTH then count=1;
else count+1;

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

You have to include the variables in the BY statement if you want SAS to set values for FIRST. and LAST. variables for them.

by DAY MTH;

You have to tell SAS not to reset the new variable COUNT to missing when it starts the next iteration.  You can use a RETAIN statement for that. 

retain count;

Or you could modify your code to use a SUM statement, which will automatically retain the variable that you are using as the accumulator in the SUM statement.

if first.MTH then count=1;
else count+1;
MART1
Quartz | Level 8

thanks @Tom

 

I've used the SUM option and it works perfectly

 

(ps: the group by must be 

 

by MTH DAY;

thanks

ballardw
Super User

Part of the answer will come if you read the log.

This line in your code:

if first.MTH then

will cause a note in your log:

NOTE: Variable first.MTH is uninitialized.

That should give you clue that you are expected to do something with MTH variable. And since First and Last only apply to variables on a BY statement ...

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 9176 views
  • 1 like
  • 3 in conversation