topic Re: If first. then group by; how to restart count in SAS Programming

If first. then group by; how to restart count

MART1 — Mon, 18 May 2020 16:44:06 GMT

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;

Re: If first. then group by; how to restart count

Tom — Mon, 18 May 2020 16:49:34 GMT

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;

Re: If first. then group by; how to restart count

ballardw — Mon, 18 May 2020 17:04:22 GMT

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 ...

Re: If first. then group by; how to restart count

MART1 — Tue, 19 May 2020 12:54:19 GMT

thanks @Tom

I've used the SUM option and it works perfectly

(ps: the group by must be

by MTH DAY;

thanks