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;
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;
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;
thanks @Tom
I've used the SUM option and it works perfectly
(ps: the group by must be
by MTH DAY;
thanks
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 ...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.