topic Re: dates issue in SAS Programming

dates issue

HeatherNewton — Wed, 30 Mar 2022 06:45:42 GMT

I am trying to make sense of the code. but got nowhere. please help.

month=(input(years,4.)-int(input(years,4.)/100)*100)*12 +int(input(years,4.)/100);
what is the above doing? I tried with different value for "years" and got nowhere.

eg.
data new;
years=1961;
month=(input(years,4.)-int(input(years,4.)/100)*100)*12 +int(input(years,4.)/100);
run;

only getting '.' i.e. missing
for month

Re: dates issue

Kurt_Bremser — Wed, 30 Mar 2022 07:09:17 GMT

The INPUT function with a numeric informat is used to read numeric data from a character argument. In your code, years is a numeric variable, so SAS first has to convert this to a character value. This automatic conversion uses the BEST12. format, so the result of the conversion is

"        1961"

The INPUT then reads the first 4 characters (which are all blanks), so you get a missing value.

Maxim 3: Know Your Data.

Inspect the input dataset of the original step from which you took the assignment to see the definition of variable years.

If the variable is created in that step, show the code that does it.

Could it be that the formula is designed to count months from a MMYY value:

data test;
input years $4.;
month=(input(years,4.)-int(input(years,4.)/100)*100)*12 +int(input(years,4.)/100);
datalines;
0000
0100
0001
0101
0201
0002
;

Re: dates issue

ballardw — Wed, 30 Mar 2022 14:43:51 GMT

My guess is that someone had a value that was a duration measured in years, such as 12, and was character value for some reason, and wanted to estimate a duration in months.

Note that the INPUT requires CHARACTER values so of course your test with years=1961; does nothing as years here would be numeric. The implied conversion to character and appears in your LOG, which uses the BEST12. format, for input would have about 8 leading blanks. So the input reads 4 of those blanks and gets a missing result. So the result of all those inputs is missing and no calculation actually performed.

data new;
years="10.2";
month=(input(years,4.)-int(input(years,4.)/100)*100)*12 +int(input(years,4.)/100);
run;

Returns a value of 122.4 months.

The Int function returns the integer portion of a result so the int(input(years,4.)/100) removes the decimal from the division so that the remainder of the middle term turns a "whole month" number of some sort.

Important learning point: When you write code that is obscure like this you should include a comment as to what it does and why it was needed.

Re: dates issue

PaigeMiller — Wed, 30 Mar 2022 15:04:23 GMT

@HeatherNewton

another learning point ... if you were to write such code (or similar) in the future, use actual SAS date values (or actual SAS date time values) and functions such as INTCK and INTNX to determine dates (for example, what date is 15 months previous?) (for example, what date is 100 days previous?), rather than this complicated mathematical formula that is difficult to understand.

In addition SAS has done the hard work of creating calendar functions that takes into account leap years, months of unequal number of days, and so on, and then tested and debugged these functions so we have confidence that they work properly, so you don't have to create your own functions to account for these things (and possibly get it wrong in your program).

Re: dates issue

HeatherNewton — Sun, 03 Apr 2022 08:08:42 GMT

I have confirmed that the years is actually no of years. could you please check what is the significance of this calculation of month.. I see no difference with just applying *12 to no of years whether the no of years is integer or with decimal

data result;
input YEARS_OF_IMP $5.;  
month=(input(YEARS_OF_IMP,4.)-int(input(YEARS_OF_IMP,4.)/100)*100)*12 +int(input(YEARS_OF_IMP,4.)/100);  
X=input(YEARS_OF_IMP,4.);  
 
month1 = (X-(int(X/100))*100)*12+int(X/100); 
test2=int(X/100);  
test2a=int(X/100)*100;  
test3=X-int(X/100)*100;  
test3a=(X-int(X/100)*100)*12;   
 
datalines;  
0 
20 
30 
15.5 
12.8 
12.3 
14.1 
; 
 
proc print data=result;

Re: dates issue

Kurt_Bremser — Sun, 03 Apr 2022 08:14:39 GMT

Look at the variable in the dataset to which this formula is applied. Knowing the data (Maxim 3) is essential in getting a grasp on the logic behind it, and finding ways to get the same result in a way that is easier to understand and maintain.

So go back to the program where you encountered this, and look at the dataset that is used.

Re: dates issue

HeatherNewton — Sun, 03 Apr 2022 19:29:10 GMT

I did. The data set is no of years. The calculation not seems not doing mich. What do u think?

Re: dates issue

Kurt_Bremser — Sun, 03 Apr 2022 19:35:40 GMT

Please post an example of your data in a data step with datalines, so we have something to test the code against.

Re: dates issue

HeatherNewton — Sun, 03 Apr 2022 23:11:22 GMT

I did yesterday. Could u see?

Re: dates issue

HeatherNewton — Sun, 03 Apr 2022 23:14:06 GMT

data result;
input YEARS_OF_IMP $5.;
month=(input(YEARS_OF_IMP,4.)-int(input(YEARS_OF_IMP,4.)/100)*100)*12 +int(input(YEARS_OF_IMP,4.)/100);
X=input(YEARS_OF_IMP,4.);

month1 = (X-(int(X/100))*100)*12+int(X/100);
test2=int(X/100);
test2a=int(X/100)*100;
test3=X-int(X/100)*100;
test3a=(X-int(X/100)*100)*12;

datalines;
0
20
30
15.5
12.8
12.3
14.1
;

proc print data=result;

Re: dates issue

HeatherNewton — Sun, 03 Apr 2022 23:14:31 GMT

Just posted sgsin

Re: dates issue

Kurt_Bremser — Mon, 04 Apr 2022 06:40:44 GMT

Are these actual, real values you took from your dataset? I ask because from a second look I am now VERY sure that the formula is designed to calculate a number of months from a 4-digit integer that is written in a MMYY format:

month =
  (
    input(YEARS_OF_IMP,4.)
    -
    int(
      input(YEARS_OF_IMP,4.) / 100
    )  *  100 /* subtract the left two digits (100 and 1000 order of magnitude) from the number */
  ) * 12 /* and multiply the remaining by 12, indicating that this is a year number */
  +
  int(
    input(YEARS_OF_IMP,4.) / 100
  ) /* and add the first two digits, so these are the months */
;

An input with a fraction makes no sense in this context.

Re: dates issue

HeatherNewton — Mon, 04 Apr 2022 09:46:19 GMT

yes the user only provide data of 0,20,30

let me ask them again

why are you sure it is mmyy format ?

Re: dates issue

HeatherNewton — Mon, 04 Apr 2022 09:49:36 GMT

what do you mean by mmyy format, I tried with 1219 and got missing for month

Re: dates issue

Kurt_Bremser — Mon, 04 Apr 2022 11:03:00 GMT

Can't be:

 69         data _null_;
 70         years_of_imp = "1219";
 71         month =
 72           (
 73             input(YEARS_OF_IMP,4.)
 74             -
 75             int(
 76               input(YEARS_OF_IMP,4.) / 100
 77             )  *  100 /* subtract the left two digits (100 and 1000 order of magnitude) from the number */
 78           ) * 12 /* and multiply the remaining by 12, indicating that this is a year number */
 79           +
 80           int(
 81             input(YEARS_OF_IMP,4.) / 100
 82           ) /* and add the first two digits, so these are the months */
 83         ;
 84         put month=;
 85         run;
 
 month=240

Re: dates issue

Kurt_Bremser — Mon, 04 Apr 2022 11:29:53 GMT

@HeatherNewton wrote:

why are you sure it is mmyy format ?

See my dissection of the formula.

Re: dates issue

HeatherNewton — Wed, 06 Apr 2022 09:56:04 GMT

but 240 mean the no of month from 1219 to when? I still dont get the logic after I tried a few more

1219 240 months

1021 262 months

1217 216 months

1221 264 months

Re: dates issue

Kurt_Bremser — Wed, 06 Apr 2022 10:20:24 GMT

Consider the start of 2000 as the "zero" point, so January of 2000 is month #1. Then this makes sense:

data result;
input YEARS_OF_IMP $5.; 
month =
  (
    input(YEARS_OF_IMP,4.)
    -
    int(
      input(YEARS_OF_IMP,4.) / 100
    )  *  100 /* subtract the left two digits (100 and 1000 order of magnitude) from the number */
  ) * 12 /* and multiply the remaining by 12, indicating that this is a year number */
  +
  int(
    input(YEARS_OF_IMP,4.) / 100
  ) /* and add the first two digits, so these are the months */
;
datalines;
0100
0200
0101
0422
;

You will get results of 1, 2,13 and 268. April 2022 is the 268th month after December 1999.

TBH, someone who writes such a formula into code, without leaving proper documentation (or at least a comment in the code) that details the function and intent, should be hung, drawn and quartered. Or at least have their behind paddled in front of the whole department. Let this be a lesson to yourself: whenever you have to write code that is not immediately understandable at first glance, write a comment to explain what happens. Also do that whenever you are forced to write code that causes a non-standard NOTE (like "More than one datasets has repeating BY values" in a MERGE)