IF THEN DO ISSUES

[Deesee]

This section (table ip) defines formats and calculates month and week limits.  When doing a proc freq on ip table this is what I get:

proc freq data=ip; tables wbeg*wend/list missing; run;

Season_yr	Wbeg	Wend	Wk2	Wk1	Year	Year3	Month3	Month33	freq
Yr_24_25	29DEC24	04JAN25	24_wk53	Wk53	2024	2025	202501	JAN	30726
Yr_24_25	29DEC24	04JAN25	25_wk00	Wk00	2025	2025	202501	JAN	41000
Yr_24_25	05JAN25	11JAN25	25_wk01	Wk01	2025	2025	202501	JAN	72588

The issue I’m having is in the next step in table ip2 where I adjust for special cases where the weeks aren’t named properly. 

For example, in the above image, the first row should be 25_wk01 NOT 24_wk53 (there should only be 52 weeks for each season_yr).

The second row should go in the 25_wk01 bucket as well (there shouldn’t be wk00 in the data).  The third row should go into 25_wk02. 

So, it should look like this:

Season_yr	Wbeg	Wend	Wk2	Wk1	Year	Year3	Month3	Month33	freq
Yr_24_25	29DEC24	04JAN25	25_wk01	wk01	2025	2025	202501	JAN	71276
Yr_24_25	05JAN25	11JAN25	25_wk02	wk02	2025	2025	202501	JAN	72588

Here are the tables producing the above (the 2025 adjustments section is where I need to do the if then do statements; the others before it are not giving any issues and have always been in the program and do not apply to 2025):

data _null_;
format dt date9.;
dt='16JAN2025'D;
call symput('currmo', compress(put(year(dt),z4.))||compress(put(month(dt),z2.)));
run;
%put CURRMO = &currmo.;


data ip;

  options compress=yes;  
  set mixed;  
  
  /* Define formats */  
  format wbeg wend mbeg mend date8.;  
  format AdmitDt  mmddyy10.; 
 
  
  /* Calculate month and week  */  
  mbeg = intnx('month', AdmitDt, 0, 'b');  
  mend = intnx('month', AdmitDt, 0, 'e');  
  wbeg = intnx('week', AdmitDt, 0, 'b');  
  wend = intnx('week', AdmitDt, 0, 'e');  
  madm = month(AdmitDt);  
  mwbeg = month(wbeg);  
  mwend = month(wend);  
  
  /* Determine the month3 value */  
if month(wbeg) = month(wend) then do;
                                  month3 = put(year(AdmitDt),4.)||put(month(AdmitDt),z2.);
                                  end;
else if mend - wbeg < 7 then do;
                                if mend - wbeg < 3 then do;
							                            if month(AdmitDt) = 12 then month3 = put(year(AdmitDt)+1,4.)||"01";
													    else month3 = put(year(AdmitDt),4.)||put(month(AdmitDt)+1,z2.);
													    end;
							    else month3 = put(year(AdmitDt),4.)||put(month(AdmitDt),z2.);
							    end;
else if wend - mbeg < 7 then do;
                             if wend - mbeg < 3 then do;
                                                     if month(AdmitDt) = 1 then month3 = put(year(AdmitDt) - 1,4.) || "12";
                                                        else month3 = put(year(AdmitDt),4.)||put(month(AdmitDt)-1,z2.);
													 end;
							 else month3 = put(year(AdmitDt),4.)||put(month(AdmitDt),z2.);
							 end;
  
  mth3 = substr(month3, 5, 2);  
  month33 = put(mth3, $mth.);  
  year3 = substr(month3, 1, 4);  
  

	  /* Additional processing  */  
  length wk2 $8.;  
  format year $4.;  
    

    
  mth2 = put(month(AdmitDt), z2.);  
  year = put(year(AdmitDt), 4.);  
    
  wk1 = compress('wk' || put(week(AdmitDt), z2.));  
  wk2 = compress(substr(year, 3, 2) || '_' || wk1);   
run;  


data _null_;
format dt date9.;
 dt='16JAN2025'D;
 cut_dt=intnx('month','16JAN2025'D,-23);
 CALL symput('currmo', compress(put(year(dt),z4.))||compress(put(month(dt),z2.)));
 CALL symput('cutmo', compress(put(year(cut_dt),z4.))||compress(put(month(cut_dt),z2.)));
 run;

%put &currmo.;
%put &cutmo.;

data _null_;
  td='16JAN2025'D;
  format cutdt date9. cutdt2 date9.;
  cutdt=intnx('month','16JAN2025'D,0); 
  cutdt2=intnx('month','16JAN2025'D,0);
  cutmth=compress(put(year(cutdt), 4.)||put((month(cutdt)), z2.));
  call symput('cutmth', cutmth);
  
run;

%put &cutmth.;

data ip2;  
  set ip;  
  format season_yr $20.;  
  
  /* Determine season year */  
  if '202009' <= month3 <= '202108' then season_yr = "Yr_20_21";  
  else if '202109' <= month3 <= '202208' then season_yr = "Yr_21_22";  
  else if '202209' <= month3 <= '202308' then season_yr = "Yr_22_23";  
  else if '202309' <= month3 <= '202408' then season_yr = "Yr_23_24";  
  else if '202409' <= month3 <= '202508' then season_yr = "Yr_24_25";  
  else season_yr = 'Other';  
  
  /* Standardize weeks  */  
  if year = "2020" then do;  
    wk1 = compress('wk' || put(week(AdmitDt) + 1, z2.));  
    wk2 = compress(substr(year, 3, 2) || '_' || wk1);  
  end;  
  else if year in ("2021", "2022", "2023", "2025") then do;  
    wk1 = compress('wk' || put(week(AdmitDt), z2.));  
    wk2 = compress(substr(year, 3, 2) || '_' || wk1);  
  end;  
  else if year = "2024" then do;  
    wk1 = compress('wk' || put(week(AdmitDt) + 1, z2.));  
    wk2 = compress(substr(year, 3, 2) || '_' || wk1);  
  end;  

  
  /* Adjust for special cases */  
  if season_yr = "Yr_21_22" and wk1 = "wk35" and year = "2021" then season_yr = "Yr_20_21";  

  if year = "2021" and wk1 = "wk00" then do;  
    wk1 = "wk53";  
    wk2 = "20_wk53";  
    year = "2020";  
    mth2 = "12";  
  end;  
  if year = "2022" and wk1 = "wk00" then do;  
    wk1 = "wk52";  
    wk2 = "21_wk52";  
    year = "2021";  
    mth2 = "12";  
  end;  

  if year = "2020" and wk1 = "wk53" then delete;  

  if year = "2023" and wk1 = "wk53" then do;  
    wk1 = "wk01";  
    wk2 = "24_wk01";  
    year = "2024";  
    mth2 = "01";  
  end;  

/*THIS SECTION NEEDS FIXING: 2025 adjustments - this is where I need to do the if then do for current dates; i commented out the ones below because they didn't work*/

/* if year = "2025" and wk1 = "wk00" then do; */
/*                                        wk1 = "wk01";  */
/*                                        wk2 = "25_wk01"; */
/* 									   year = "2025"; */
/* 									   mth2 = "01"; */
/* 									   end; */
/*  */
/* if year = "2025" and wk1 = "wk01" then do; */
/*                                        wk1 = "wk02";  */
/*                                        wk2 = "25_wk02"; */
/* 									   year = "2025"; */
/* 									   mth2 = "01"; */
/* 									   end; */

  

month = year || mth2; run;

There are no errors or anything just a matter of bucketing using if then do statements.  Any help is appreciated. I've just been stuck on this for days.

[webart999ARM]

To resolve the week numbering issues for the 2025 season in the ip2 dataset, add the following code within the data step for ip2 under the "2025 adjustments" section. This will correct the week labels by adjusting wk1, wk2, year, and mth2 based on the specified conditions:

data ip2;  
  set ip;  
  format season_yr $20.;  
  
  /* Determine season year */  
  if '202009' <= month3 <= '202108' then season_yr = "Yr_20_21";  
  else if '202109' <= month3 <= '202208' then season_yr = "Yr_21_22";  
  else if '202209' <= month3 <= '202308' then season_yr = "Yr_22_23";  
  else if '202309' <= month3 <= '202408' then season_yr = "Yr_23_24";  
  else if '202409' <= month3 <= '202508' then season_yr = "Yr_24_25";  
  else season_yr = 'Other';  

  /* ... existing code ... */

  /* 2025 Adjustments */
  if season_yr = "Yr_24_25" then do;
      /* Adjust weeks spanning into 2025 (wk53 from 2024 becomes 25_wk01) */
      if wk1 in ("wk53", "wk00") then do;
          wk1 = "wk01";
          wk2 = "25_wk01";
          year = "2025";
          mth2 = "01";  /* Set to January */
      end
      /* Increment subsequent weeks by 1 (wk01 becomes 25_wk02) */
      else if wk1 = "wk01" then do;
          wk1 = "wk02";
          wk2 = "25_wk02";
          year = "2025";
          mth2 = "01";
      end;
  end;

  month = year || mth2; 
run;

Explanation:

• Condition on season_yr: Targets only the "Yr_24_25" season.

• Handling wk53 and wk00: Converts both to wk01 (2025) with wk2 set to "25_wk01".

• Adjusting wk01 to wk02: Ensures the following week is incremented correctly.

• Updates year and mth2: Ensures consistency with the new week assignments (January 2025).

This adjustment ensures weeks crossing into 2025 are labeled correctly, eliminating invalid week numbers (e.g., 24_wk53, 25_wk00) and aligns with the desired output structure.

Alternative (Less Hardcoded) Approach:

To reduce hardcoding, you could:

1. Derive the target year from wbeg/wend dates instead of hardcoding 2025.

2. Use date functions to calculate week numbers dynamically.

Example snippet:

/* 2025 Adjustments (Less Hardcoded) */
if season_yr = "Yr_24_25" then do;
  /* Calculate target year based on the week's end date */
  target_year = year(wend);
  target_week = week(wend);  /* SAS week function */

  if wk1 in ("wk53", "wk00") then do;
    wk1 = "wk01";
    wk2 = catx("_", put(target_year, z2.), "wk01");
    year = put(target_year, 4.);
    mth2 = "01";
  end
  else if wk1 = "wk01" then do;
    wk1 = "wk02";
    wk2 = catx("_", put(target_year, z2.), "wk02");
    year = put(target_year, 4.);
    mth2 = "01";
  end;
end;

This still assumes some structure (e.g., target_year is derived from wend), but reduces reliance on fixed values like "2025".

If the week numbering issue is specific to the 2024-2025 transition and not a recurring pattern, the original hardcoded solution is practical and valid. For future-proofing, consider a dynamic approach.

Hope this helps.