Hi,
I currently have an extract of code (shown below), but I would like to make the dates macros and create a new program specifically to enter the date, which will save time for the user instead of having to scroll through the lines of code and updating the dates.
Current code:
proc sql;
create table date_analysis as
select
a.*
,b.* ,c.*
from (select * from dataset_alpha_2021 where month_end=22584) a
inner join (select * from dataset_beta_2021 where month_end='31OCT2021'd) b
on a.account_num=b.account_num left join dataset_gamma_Oct21 c on a.account_num=c.account_num
;
quit;
Proposed changes to have macros:
I will create a new program for the user to set the date (this will be manually updated for the month-end e.g. 31Oct2021 (for October 2021), 30Nov2021 (for November 2021) etc.. where I would like four macros to be created:
1. The best starting point might be to create a macro called month_end
2. yyyy (for the two scenarios where 2021 is used)
3. run_date. In dataset_alpha_2021, the month_end variable only lists numeric date value e.g. 22584, which I had to calculate as being equal to 31 Oct 21. Could this line be edited so that the format of month_end is equal to the date macro in the dataset_beta_2021 line of code?
4. monyy
%let month_end=31Oct2021; /*The date will be manually updated by the user e.g. 30Nov2021, 31Dec2021 etc. The three macros below should automatically calculate based on the date input for this month_end macro*/
/*(used in the dataset_alpha_2021 line of code)*/
%let run_date=[not sure how to have date value e.g. 22584 equalling month_end macro above?]
/*(used in the dataset_beta_2021 line of code)*/
%let yyyy=[something like %sysfunc(intnx(month,"month_end"d,0,e),monyy5.); ?
/*(used in the dataset_alpha_2021 and dataset_beta_2021 lines of code)*/
&let monyy=[something like %sysfunc(year("month_end"d)); ?
/*(used in the dataset_gamma_2021 line of code)*/
After the macros have been applied, it could look something like this (note that I am not sure what the best way is to have the macro for the date value (maybe call it something like "run_date") being equal to the month_end macro (i.e. since month_end has been set as "31Oct2021", the run_date macro would automatically pick up the 22584 values from the dataset_beta_2021 dataset as they are equal to the same date).
proc sql;
create table date_analysis as
select
a.*
,b.* ,c.*
from (select * from dataset_alpha_&yyyy. where [not sure which format(month_end)=["&run_date"d i.e. the number 22584 is equal to 31Oct2021]) a
inner join (select * from dataset_beta_&yyyy. where month_end="&month_end"d) b
on a.account_num=b.account_num
left join dataset_gamma_&monyy. c
on a.account_num=c.account_num
;
quit;
Thanks in advance.
... View more
