Import file with variable directory

[Tri_Luong]

Hi all,

I trying to import one excel file in folder: D:\/SAS Practice\Input data\data_testing09082018.xls  using dynamic path as below method:

%let data_source = "D:\/SAS Practice\Input data\"
%let report_date = '09082018'd.;

proc import out=imported_data
         datafile= data_source report_date
         dbms=xls replace;
         sheet="sheet1";
run;

data new_data;
          set imported_data;
          date_create = report_date;
          format date ddmmyyn8.
run;

My purpose is that:

1. Variable "date_create" is formatted "dd/mm/yyyy" with the value as declared "report_date" above (09/08/2018).

2. Change the files to be imported when I change the value of "report_date".

3.I am able to use "report_date" = today() if needed.

Thank everyone.

[Kurt_Bremser · Accepted solution]

See my comments:

%let data_source = D:\SAS Practice\Input data\;
* missing semicolon here;
* don't use forward slashes;
* don't use quotes, as these would skewer your string later;

%let report_date = 09092018;
* same here: no quotes, please!;

proc import
  out=imported_data
  datafile="&data_source.data_testing&report_date..xls"
  /* reference macro variables with an ampersand, terminate macro variable references with a dot */
  /* file path needs to be a quoted string */
  dbms=xls
  replace
;
sheet="sheet1";
run;

data new_data;
set imported_data;
date_create = input("&report_date",ddmmyy8.);
/* since &report_date does not contain a SAS date literal (needs date9 format), use the input() function */
format date ddmmyyn8.;
run;

---

@Tri_Luong wrote:

Hi all,

I trying to import one excel file in folder: D:\/SAS Practice\Input data\data_testing09082018.xls  using dynamic path as below method:

%let data_source = "D:\/SAS Practice\Input data\"
%let report_date = '09082018'd.;

proc import out=imported_data
         datafile= data_source report_date
         dbms=xls replace;
         sheet="sheet1";
run;

data new_data;
          set imported_data;
          date_create = report_date;
          format date ddmmyyn8.
run;

My purpose is that:

1. Variable "date_create" is formatted "dd/mm/yyyy" with the value as declared "report_date" above (09/08/2018).

2. Change the files to be imported when I change the value of "report_date".

3.I am able to use "report_date" = today() if needed.

Thank everyone.

---

[Tri_Luong]

Hi KurtBremser,

What a helpful and detailed answer! It worked. However, if I would like to change "rprt_date" = today(), how can I do with the same purpose?!

[Kurt_Bremser]

To use data step functions in macro code, you need the %sysfunc() macro function; this also allows direct formatting of the result:

%let report_date=%sysfunc(today(),ddmmyyn8.);

[Tri_Luong]

Dear KurtBremser,

Could you please instruct me how to define "report_date" as previous working day since today (one working day before today, excluded holidays and weekends) ?! I know that I have to declare holidays somewhere but I don't know how to do it. 

Thank you.

[Kurt_Bremser]

Use the weekday() function to determine Saturdays and Sundays, and the holiday() function for holidays depending on the date of "easter". Certain national holidays need to be determined by month and day.

[Tri_Luong]

Thank Mr. KurtBremser,

If I would like to utilise the code below:

%let report_date=%sysfunc(today(),ddmmyyn8.);

to turn "report_date" is:

+ 1 previous working day (WHITHOUT holiday and weekend between)

+ 1 previous working day (WITH holiday and weekend between)

Could you please instruct me the code for the 2 situations above!?

 I have tried below code:

%let rprt_today = %SYSFUNC(TODAY(),yymmddn8.); 
%let rprt_yesterday = put(intnx('DAY',today(),-1),yymmddn8.);

data temp;
today = &rprt_today;
yesterday = &rprt_yesterday;
run;
　
PROC IMPORT OUT= LOAN_OVERDUE_YESTERDAY
DATAFILE="&RPRT_DIR_LOAN.OVD-LN-&rprt_yesterday. - COPY.XLS"
DBMS=XLS REPLACE;
SHEET="SHEET";
RUN;

but I got the notification:

"ERROR: Physical file does not exist, z:\reports\loan collections\daily loan overdue report\OVD-LN-put(intnx('DAY',today(),-1),yymmddn8.) - COPY.XLS."

From my observation, it might be because "rprt_today" turned out numeric value whilst "rprt_yesterday" turned out character value. Is it true?

[Kurt_Bremser]

Do your calculations for the previous workday in a data step, and use call symput() to store that value in a macro variable.

Go back from today() in a loop, and check if the date is not a workday, according to local laws; as soon as you find a workday, leave the loop, and set the macro variable.