Alter variable value for the current month to value of another variable

[Kody_devl]

Hi All,

I have a lookup table that I use to set variable to the correct month-end day for each month of the year.  However,  I can't use the last day of the month for the Current month (in this case, NOV). 

My question is, how can I alter the current month value of the variable, Nov_EPer, to the value of another variable value, &L_SYS_FILE. ("Last System File" (date))?

Here is how I am assigning the last day for each month from the lookup table (LOAN_DETAIL_SRCES_CPER_TDY):

select Sep_Per

into: Sep_EPer  <-----'09/30/2016'

from WORK.LOAN_DETAIL_SRCES_CPER_TDY;

select Oct_Per

into: Oct_EPer  <-----'10/31/2016'

from WORK.LOAN_DETAIL_SRCES_CPER_TDY;

select Nov_Per

into: Nov_EPer  <-----'11/30/2016' (change it to &L_SYS_FILE. = '11/23/2016')

from WORK.LOAN_DETAIL_SRCES_CPER_TDY;

select Dec_Per

into: DEC_EPer  <-----'12/31/2016'

So for November, I need  Nov_EPer = &L_SYS_FILE. Then of course, this logic must switch to Dec on 12/01/2016 and forward.

PS:  I have another varible that holds the current month number, &C_Month. = 11 so the code knows the current month number.

Many Thanks

Kody_Devl

[Kurt_Bremser]

data _null_;
do month = 1 to 12;
  if month = month(today)
  then date = input("&l_sys_file.",mmddyy10.);
  else date = intnx('month',mdy(month,1,year(today())),0,'end');
  name = put(date,monname3.) !! 'eper';
  call symput(name,put(date,mmddyy10.));
end;
run;

Corrected to use macro variable &l_sys_file instead of today() in current month.

[RW9]

Sorry, the question is not clear to me.  Why are you using macro - which is only text - to do dates and months calculations?  Why are XXX_per variables character ('09/30/2016' is a character string).  Seems to me like your making it as complicated as possible.  Convert the dates as test to numerics so you can work with the properly.  Then assess the requirement, and see how using a datastep with all the associated functions and datatypes can simplfy the whole process.

[Kody_devl]

These variables are being passed into PROC SQL statements (later down the code line) and are matched to a Put(t2.Posting_Date, MMDDYY.10)  to retrieve the correct transactions.  I have to keep the formatting AS IS to function in my proc SQL statements (which are currently working fine (it is just that I am passing the wrong dates for NOV '11/30/2016' instead of '11/23/2016').

I am not yet an expert on SAS dates and variable formatting but, what I have is working.

Thanks

[RW9]

Hi,

There is no need to be an expert on anything.  Base SAS is the programming language, it has all the datatypes and functions needed to do any process.  Macro is none of these, it is a text find and replace system which generates text which then goes into the SAS compiler.  As part of the Base SAS language, and understanding of datatypes is essential - as with any programming language.  Dates are number of days since a certain timepoint, times are numbers of seconds from midnight, and datetimes, are a combination thereof.  To work with these you can use simple mathmatical notation, or use more complicated functions.  None of the above is true with Macro language, hence why it is not a good medium for this task.

Now you obviously know SQL - if that is why you are doing it this way - so you will be familiar with the concept of joining.  If we put the paramters you talk about into a dataset, we can then simply join that dataset to our data and perform all the necessary logic to arrive at our end product, for instance:

data params;
  cat="range";
  start_dt="01JAN2014"d;
  end_dt="10JAN2014"d;
run;

proc sql;
  create table WANT as
  select  A.*
  from    HAVE A
  left join PARAMS B
  on      1=1
  where   B.START_DT <= A.ACTUAL_DATE < END_DT;
quit;

In the above, you dont' even need to join it, you could just subquery.  Basically there is nothing in Macro that can't be done quicker, simpler in Base SAS.

[Tom · Accepted solution]

Not sure why you need to truncate only the current month and not also truncate future months but you could probably just update your SQL statement to include the logic to pick which value to use by adding a CASE statement.

select case when month(today())=11 then "&L_SYS_FILE"
            else Nov_Per
       end
  into: Nov_EPer
  from WORK.LOAN_DETAIL_SRCES_CPER_TDY
;

Make similar changes for the other months. 

You should probably look into changing the whole system to be simplier so that it doesn't require all of these macro variables.

[Kody_devl]

I would like to make this work as it is the simplest option for me.

Here is my attempt: FOCUS ON NOV

*/

select case

when &L_Period. = 11 then &L_SYS_FILE      <------ Note I did remove the "&L_SYS_FILE"

else t1.Nov_Per

end

into: Nov_EPer

from WORK.LOAN_DETAIL_SRCES_CPER_TDY t1;

/*

select Dec_Per

into: Dec_EPer

from WORK.LOAN_DETAIL_SRCES_CPER_TDY;

*/

Run;

%Put Period 11 is &Nov_EPer.;

HERE is the LOG Result for &Nov_EPer.

76

77 %Put Period 11 is &Nov_EPer.;

     Period 11 is 0.000237 <------ This should be 11/23/2016

[Tom]

Sounds like the value of your macro variable is 11/23/2016.  So SAS did what you asked it to do, it divided 11 by 23 and then by 2016.  

If you want SAS to treat that string as a valid date value then you will need to use INPUT() function to convert it.

What do you want the resulting macro variable's value to look like?

Do you want an actual date value, ie the number of days since 01JAN1960?  Then just use the INPUT() function and you will get a number like 20781.

input("&l_sys_file",mmddyy10.)

Or do you want the character string 11/23/2016?  That is the same thing that is in L_SYS_FILE macro variable.

"&l_sys_file"

Do you want the character string to include quotes? 

"'&l_sys_file'"

Or some other format?

[Kody_devl]

Tom

Your last post was very helpful.

This is what I need (works)!

/* NOV */

select case

when &L_Period. = 11 then "&l_sys_file"

else Put(t1.Nov_Per, mmddyy10.)

end

into: Nov_EPer

from WORK.LOAN_DETAIL_SRCES_CPER_TDY t1;

PS,

I need all of the variables because I am processing 12 months at once (the whole year).  Hence, I need the 12 variables.  For post period months (ie Dec (months in future)) there are never any DEC transactions to pull so it doesn't matter is the query is using 12/31/2016.

The code will now self correct for any L_SYS_FILE date without code changes from month-to-month. (and I am starting to understand the formatting)

Thank you sooooo much. 

[Kody_devl]

Tom,

Why would this query return t1.In_Status_Day = 12/31/2015?

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_LOAN_DETAIL_STATUS AS

SELECT t1.Loan_Number,

t1.In_Status_Day

FROM WORK.LOAN_DETAIL_STATUS t1

WHERE Put(t1.In_Status_Day, mmddyy10.) BETWEEN '12/01/2016' AND '12/31/2016';

QUIT;

[Tom]

And why wouldn't it?
'12/31' definitely comes after '12/01' so it is larger than the lower bound.
'12/31/2015' is almost exactly the same string as '12/31/2016', it just differs in the last characters. Since '5' is less than '6' it is definitely less than the upper bound.

Why are you comparing date values to character strings? If you want to do that then make sure your character strings are in YYYYMMDD format so that the lexical ordering matches the chronological ordering.

[Kody_devl]

I am an Old dog. 

I am still thinking dates.

My new brain links are slowly connecting. 

Thank you

[Peter_C]

rather than

WHERE Put(t1.In_Status_Day, mmddyy10.) BETWEEN '12/01/2016' AND'12/31/2016';

consider trying

WHERE t1.In_Status_Day BETWEEN '01Dec2016'd  AND '31Dec2016'd ;

this demonstrates date constants