Modify Daily Code to Monthly.

[yeppy12]

Hello. I have some code below that imports some files for a particular day and then combines them into a final dataset. What I want to do is modify the code below to import an entire month of files instead of a day. How can I do that? Any help is greatly appreciated.

options compress = yes;
%let day = 20170101; 
libname cc "my path";
%let macro_loc = macro path;
%include "&macro_loc./macro_min_storage300.sas";
%include "&macro_loc./macro_min_storage.sas";
%include "&macro_loc./import_chd_base_segment.sas";
%include "&macro_loc./import_chd_behavior_score_history.sas";
%include "&macro_loc./import_chd_delinquency_data.sas";
%include "&macro_loc./import_chd_history_data.sas";
%include "&macro_loc./import_chd_current_activity_segment.sas";
%include "&macro_loc./import_customer_data_segment.sas";
%include "&macro_loc./import_customer_data_segment.sas";
%chd_base_segment(day = 20170101);
%chd_behavior_score_history(  day = 20170101);
%chd_delinquency_data(day =20170101 );
%chd_history_data(day = 20170101);
%chd_current_activity_segment(day = 20170101);
%chd_customer_data_segment(day = 20170101);

data current_activity;
set cc.current_activity_&day (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
run;
data chd_delinquency;
set cc.chd_delinquency_&day (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
run; 
data chd_behavior_score_hist;
set cc.chd_behavior_score_hist_&day (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
run;
data chd_hist_data;
set cc.chd_hist_data_&day (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
run;
data chd_cust_seg;
set cc.chd_cust_seg_&day  (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
run;
proc sql;
   create table cc.cc_extract_&day as
   select a.*,b.*,c.*,d.*,e.*,f.*
   from cc.cc_base_chd_seg_&day as a
           left join current_activity as b on a.chd_account_number = b.chd_account_number
           left join chd_delinquency as c on a.chd_account_number = c.chd_account_number
           left join chd_behavior_score_hist as d on a.chd_account_number = d.chd_account_number
           left join chd_hist_data as e on a.chd_account_number = e.chd_account_number
              left join chd_cust_seg as f on a.chd_account_number = f.chd_account_number ;
quit;

[ballardw]

Are looking to modify this code

data current_activity;
set cc.current_activity_&day (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
run;

to do something like

data current_activity;
set 
   cc.current_activity_20170101 (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
   cc.current_activity_20170102 (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
   cc.current_activity_20170103 (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
   cc.current_activity_20170104 (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
   cc.current_activity_20170105 (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
/* continue to*/
   cc.current_activity_20170131 (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
   ;
run;

If so then if this works for you:

data current_activity;
set 
   cc.current_activity_201701: (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
;
run;

Then

data current_activity;
set 
   cc.current_activity_%substr(&day,1,6): (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
;
run;

[yeppy12]

Well, when I run this code now, I just change the day =  parts to the particular day I need.  What I would like to do is just run for an entire month instead of going day by day to save me time.  I'd much rather deal with a monthly file than trying to turn several daily files into one monthly file.  Is there a way to modify the code using a loop or something like that to let me run it just once for the month...instead of typing each day and running the code?

[ballardw]

data current_activity;
set 
   cc.current_activity_%substr(&day,1,6): (drop= chd_agent_bank chd_prin_bank create_ts period_dt record_id update_ts ) ;
;
run;

Creates a monthly data set. Or at least will combine all of the current_activity_201701 (or whichever are the first six characters of that &day variable. You would not have to change it at all. This is a pattern you could use for any of those sets to create a combined monthly set. I might be tempted to create an output data set with the year and month part to save them but since your example isn't writing to a permanent library I am not sure that's needed. If you want to keep it then put it in a permanent library.

Your process has so many include file and macro calls that how to turn any of them into using a monthly data set instead of a daily I have no idea what you may want/need to accomplish. If you have actual date values inside your data and not just in the dataset names then I would hope that processing By Date in any steps would work. If you don't have dates you could use the DSNAME option to caputure the name of the data set contributing records and then use that variable in BY group processing.

[yeppy12]

Okay.  I am going to see if I can get your suggestion to work.  It is an interesting approach...one question for you, though: do I need to leave the date in the let statement blank?

[ArtC]

Do notice that in @ballardw's reply the string

%substr(&day,1,6)

utilizes the value of &DAY, but only the first 6 characters YYYYMM, thus DD is ignored.

[yeppy12]

Good catch.  I will make that correction.  Still trying to figure out how to get the suggested approach to work.

[ArtC]

When you think that you are getting close, post your updated code.