if then count=1 in data step

[MART1]

Hello all

I'm trying to create a calendar showing the working days, but I'm having an issue with one condition.

I create a "count" column that increases the value only if the day is a working day (and if it's a non working day, it gets the same value of the latest working day.)

That works well, but I then would like to assign -1 if the first day of the month is a non working day (and it should then move to 1 once it gets to the first working day).

Below is  as extract of the data and the code I'm using: for 201903 this works fine, but for example for 201906 I'd like to see -1 for the first tow days, then  1,2,3 etc for the working days.

Basically, I don't want to see the 00; instead it should move from -1 to 1

data WORK.DATE;
	input DAY MONTH WORKING_DAY $;
	datalines;

20190101 201901 NO
20190102 201901 YES
20190103 201901 YES
20190104 201901 YES
20190105 201901 NO
20190106 201901 NO
20190107 201901 YES
20190108 201901 YES
20190109 201901 YES
20190202 201902 NO
20190203 201902 NO
20190204 201902 YES
20190205 201902 YES
20190206 201902 YES
20190207 201902 YES
20190301 201903 YES
20190302 201903 NO
20190303 201903 NO
20190304 201903 YES
20190305 201903 YES
20190306 201903 YES
20190307 201903 YES
20190601 201906 NO
20190602 201906 NO
20190603 201906 YES
20190604 201906 YES
20190605 201906 YES
20190606 201906 YES
20190607 201906 YES
20190608 201906 NO
20190609 201906 NO
20190610 201906 YES
;
run;

data BDU_TRGR.DATE2;
	set WORK.DATE;
	by MONTH DAY;

	if first.MONTH and WORKING_DAY = "NO" then
		count=-1;

	if first.MONTH and WORKING_DAY = "YES" then
		count=1;
	else if WORKING_DAY = "YES" then
		count+1;
	WD=count;
	drop count;
	format WD z2.;
run;

any help would e greatly appreciated!

thanks

[ketpt42 · Accepted solution]

Would this behavior give you what you're looking for?

(Note: Code edited to correct logic error)

data work.DATE2;
    set WORK.DATE;
    by MONTH DAY;
    
    select;
        when (first.MONTH and WORKING_DAY = "NO")
            count=-1;
        when (first.MONTH and WORKING_DAY = "YES")
            count=1;
        when (count=-1 AND WORKING_DAY = "YES")
            count=1;
        when (WORKING_DAY = "YES")
            count+1;
        otherwise;
    end;
    
    WD=count;
    drop count;
    format WD z2.;
run;

Not sure of the other context, but you probably don't need a separate count and WD variable.

[MART1]

many thanks, now I see how I should have done it!

thanks

[Shmuel]

Replace your second step with:

data DATE1;
	set DATE;
	by MONTH DAY;
    retain WD;
	if first.MONTH then do;
	   if WORKING_DAY = "NO" then  do; WD=-1; output; WD=0; end; else
	   if WORKING_DAY = "YES" then do; WD=1;  output; end;
	end;
	else 
	if working_day= 'NO' then do; wd=-1; output; wd=0; end; else
	if WORKING_DAY = "YES" then do; WD+1; output; end;
	format WD z2.;
run;

[MART1]

many thanks @Shmuel, that's another clean way to do this