IF THEN DO - Issues

[Deesee]

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 limits */  
  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") 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;  
   else if year = "2025" then do;  
    wk1 = compress('wk' || put(week(AdmitDt), 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;  

/*2025 adjustments - attempted below*/

/* 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;  

Any help is appreciated. I've been stuck on this for days. It seems it should be simple but I attempted many ways but it either doubled the counts for one week or just didnt push into the bucket I needed  to go into.

[andreas_lds]

I can hardly say anything useful to solve your problem without seeing the dataset ip.

Formats exist to show dates as week + year, so the fancy string concatenations seem to be obsolete.

[Deesee]

There is a sample of the ip table but do you need more rows from it? It's over 18 million rows. The if then do in the next step is the issue. It looks like the top table but needs to look like the bottom table. I think I'm doing the if then do right but it for some reason doesn't assign properly.

[ballardw]

---

@Deesee wrote:
There is a sample of the ip table but do you need more rows from it? It's over 18 million rows. The if then do in the next step is the issue. It looks like the top table but needs to look like the bottom table. I think I'm doing the if then do right but it for some reason doesn't assign properly.

---

To check behavior of code requires examples of the input data, best as a working data step. Variable types cannot be determined from pictures of tables and code cannot be tested.

If there are specific values that have "issues" then examples of the data set with those values are helpful.

Since Proc Freq would generate OUTPUT it does tend to hide what the INPUT might have been not to mention that the code shown will not generate the output shown.

A clearer description of the problem and desired output would be helpful. I can't highlight or copy/paste

out the sections to discuss because all you provided was a picture. But there is nothing to say WHY "first row should be 25 wk01 not 24 wk53" without input values that should be assigned the output.

Or exactly what is a "special case".

Note that there are 3 specific Formats for SAS date values that assign week values based on some rules involving start of week and end / start of year, WEEKU. WEEKV. WEEKW. 

You can also make custom formats using Proc Format involving the U, V and W rules for start/ end of year. But without a clear description of your "special case" definition can't make specific suggestions.

Generally almost anything involving that much string manipulation of dates is more complicated than working directly with the Proc Format directives for PICTURE statement.

[Deesee]

I'm sorry but all the formatting is in the SAS code posted. There are formats for all the fields in the SAS code. Also the results show what they look like after applying the formats. I thought that would be helpful.

[PaigeMiller]

Examples and instructions to provide us the data

https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

[Deesee]

Correction in the total freq numbers for the top table:

[Deesee]

data ip2;
 input  season_yr: $20.
        wbeg: DATE8.
        wend: DATE8.
	wk1: $4.
	wk2 $8.
	year $4.
	year3 $6.
	month3 $6. 
	month33 $3.
	mth2: $2.
	mth3: $6.
datalines;
Yr_24_25,29DEC24,04JAN25,24_wk53,Wk53,2024,2025,202501,JAN
Yr_24_25,29DEC24,04JAN25,25_wk00,Wk00,2025,2025,202501,JAN
Yr_24_25,05JAN25,11JAN25,25_wk01,Wk01,2025,2025,202501,JAN
;
run;

[Tom]

That data step won't run as written.  Not hard to fix the obvious mistakes, like missing semicolon,  wrong widths on informats (and hence wrong implied lengths for the variables), missing infile statement, missing format statement.  

Harder to fix having different number of variables read than values on the lines.

Did you mean something like this:

data ip2;
  infile datalines dsd truncover;
  input
    season_yr :$20.
    wbeg :DATE.
    wend :DATE.
    wk1 :$9.
    wk2 :$4.
    year1 :$4.
    year2 :$4.
    month3 :$6. 
    month33 :$3.
  ;
  format wbeg wend date9.;
datalines;
Yr_24_25,29DEC24,04JAN25,24_wk53,Wk53,2024,2025,202501,JAN
Yr_24_25,29DEC24,04JAN25,25_wk00,Wk00,2025,2025,202501,JAN
Yr_24_25,05JAN25,11JAN25,25_wk01,Wk01,2025,2025,202501,JAN
;

Result

Is that the INPUT or the OUTPUT?

Also why does the text in the WK1 variable use lowercase letters but it uses mixed case letters in the WK2 variable?

[Deesee]

Yes that is fine. It shouldnt be mixed case it's lower case.

[Tom]

This statement in your first PICTURE is problematic.

You have to have some way to have years with 53 weeks in them since the number of days in a year does not divide evenly into 7 day weeks.

How do you want to handle the extra days?  Do you want to have some years with a week 0 instead of the week 53 you are complaining about?  Do you want to allow week1 and/or week52 to have more than 7 days?

Can you spell out the rules you want to use to fix the week assignments?

What day of the week do weeks start on? Is it Sunday? Or some other day?

What variable has the actual dates?  Are those the WBEG and WEND variables?

[Deesee]

wbeg and wend have the actual date ranges. Also, the calculation for those are in the posted SAS code.

  wbeg = intnx('week', AdmitDt, 0, 'b');  
  wend = intnx('week', AdmitDt, 0, 'e'); 

[Deesee]

Figured it out; just included an else if statement.

[Tom]

I still do not understand what the question you are asking is.  Are you having trouble understanding the code you shared?  Is it not doing what you want?  You mentioned something about PROC FREQ and then showed results with many more variables than included in the TABLES statement you showed, so it is not clear what the issue is.

Are you having trouble generating the many different variables from the ADMITDT?  Which ones are giving you trouble.

Which of those variaables do you actually NEED?  For example many of them can easily be regenerated from one or more of the others.

If you want to fix your current code I would start by simplifying it.  For example if you want to generate YYYYMM strings from a DATE value just use the YYMMN6. format.

month3 = put(admitdt,yymmn6.);

If you want to adjust a date by a month use in the INTNX() function.  For example to get the next or previous month use 1 or -1 as the offset value in the call.

month3 = put(intnx('month',admitdt,1),yymmn6.);
month3 = put(intnx('month',admitdt,-1),yymmn6.);