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