topic Re: Import MS Excel file with dynamic directory path follwing actual date in SAS Programming

Import MS Excel file with dynamic directory path follwing actual date

Tri_Luong — Thu, 02 Aug 2018 06:33:47 GMT

Hi every one,

I am trying to write a code in order to import different files which are named based on reported date. For example: "Loan 20180802.xls":

* I can import successfully with below code:

%let rpt_date = %sysfunc(today();yymmddn8.);

proc import out testing
        datafile= "d:\reports\loan &rpt_date.xls" dbms=xls replace;
        sheet="sheet1";
run;

* When I would like to have dynamic "rpt_date", I have tried below codes but failed:

%let rpt_date = today();
%let rpt_date_path = %sysfunc(rpt_date;yymmddn8.);

proc import out testing
        datafile= "d:\reports\loan &rpt_date_path.xlx" dbms=xls replace;
        sheet="sheet1";
run;

Would you mind instructing me solution?!

Thank you very much.

Re: Import MS Excel file with dynamic directory path follwing actual date

andreas_lds — Thu, 02 Aug 2018 06:59:40 GMT

The code you posted should not run at all.

%let rpt_date = %sysfunc(today();yymmddn8.);

The colon after today() is wrong, a comma is required at this place.

And in

datafile= "d:\reports\loan &rpt_date.xls" dbms=xls replace;

a second dot is required to get a normal named file.

Your code should start with

option mprint mlogic symbolgen;

so that you can see the code executed.

Re: Import MS Excel file with dynamic directory path follwing actual date

RW9 — Thu, 02 Aug 2018 07:50:58 GMT

As @andreas_lds has said, the code you present is invalid. I would also ask why the process is trying to do everything the worst way possible. For instance:

You are using proc import - this is a guessing procedure, and will not import the data matching what you know the data should be except by fluke.

You are putting "data" in the filename - date in this case - which makes coding harder, and you may miss that information.

You are using an ancient version of Excel (as denoted by the xls), which is decades old and has been replaced by xlsx for at least 10 years or more. XLS is proprietary binary format so not portable across systems, and only really usable on windows.

I would suggest going back and looking thoroughly at the whole process, why do you get those files in, where do they come from, can they be got in a better way (access to a database, or using a good data transfer file format such as csv/xml). Then have an import agreement which fixes down what the structure is of the data, and write a datastep import program which reads in the data exactly as you know it should be - in this way you know exactly how the output dataset will be. For instance, it is simple to import a load of csv files in one datastep, get the date from the filename, and write out one dataset with a fixed structure:

data want;
  infile "d:/reports/loan*.csv" filename=temp;
  date=strip(compress(compress(temp,"loan",""),".csv",""));
  ...
run;

Re: Import MS Excel file with dynamic directory path follwing actual date

Tri_Luong — Thu, 02 Aug 2018 07:59:58 GMT

Thank Andreas_Ids for your response.

It was caused by my typo mistake. What I wanted was to have dynamic path with different dates. Not only "today".

Re: Import MS Excel file with dynamic directory path follwing actual date

andreas_lds — Thu, 02 Aug 2018 08:05:53 GMT

@Tri_Luong wrote:

Thank Andreas_Ids for your response.

It was caused by my typo mistake. What I wanted was to have dynamic path with different dates. Not only "today".

Difficult to suggest something without knowing which dates should be inserted. There are multiple discussions in the communities explaining how to get filenames from a directory and import all/some files.

Re: Import MS Excel file with dynamic directory path follwing actual date

Tri_Luong — Thu, 02 Aug 2018 08:06:33 GMT

Hi RW9,

Thanks for your kind advices.

1/ I am very new with SAS hence I have not known other solutions to import data. The supports/advices by you are highly appreciated.

2/ As in my company, they still use .xls file as source data, I use the options in my codes.

3/ My final purpose is writing a code so that I can import different files which are named based on different dates.

Re: Import MS Excel file with dynamic directory path follwing actual date

Tri_Luong — Thu, 02 Aug 2018 08:11:26 GMT

Let say I would like to import 3 different files with similar names: Test 20180801.xls, Test 20180802.xls, Test 20180731.xls but I don't want to change the path. Instead, I would like to change a variables (%let) as my example codes mentioned initially.

Re: Import MS Excel file with dynamic directory path follwing actual date

RW9 — Thu, 02 Aug 2018 08:23:19 GMT

2) - I would advise you to present why XLS is not a good file format, for any purpose in this day an age. It was discontinued 15 years ago in favour of the Open Office file format. It is not portable - hence any other system than windows it will not work. It has limited functionality. From my experience other groups will not accept it. No-one should be using such a difficult ancient file format as this.

3) - Yes, I can see that. But a simple change to your process means that you would not have to - time saved, code writing saved, more robust etc. Even if you only have xls there is nothing stopping someone from saving as csv from Excel, or batch job it:

https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

Is one simple example of such a batch file to convert one file. It is a simple matter to add that script and this one:

https://stackoverflow.com/questions/16665748/vbscript-to-loop-through-all-files-in-a-folder

Together and voila, all the xls files are now csv.