topic Re: Import multiple Excel files(xlsx) in SAS Programming

Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 16:27:38 GMT

Hi,

I am trying import multiple files from a folder. I try every code in others topics and never made it. All files in that folder have the extension xlsx

For save the name files the basic code it is not working. I don't know why this happen.

filename tmp pipe 'dir "C:\Users\Documents\incidentes" /s /b';

data test;
	infile tmp dlm="¬";
	length buff $2000;
	input buff $;
	fname=scan(buff, countw(buff, "\"), "\");

	if index(upcase(fname), ".XLSX")>0 then
		call execute(cats('proc import datafile="', buff, '" out=', fname, '; run;'));
run;

NOTE: The infile TMP is:
      Unnamed Pipe Access Device,
      PROCESS=dir "C:\Users\Documents\incidentes" /s /b,
      RECFM=V,LRECL=32767

Stderr output:
File Not Found
NOTE: 0 records were read from the infile TMP.
NOTE: The data set WORK.TEST has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Some has a answer? Any good macro of this?

Regards

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 16:32:55 GMT

Split your code and isolate where you're having the issue.

Is it creating the TEST data set first with the list of XLSX files correctly? <- In this case fix this first.

Then add back the CALL EXECUTE.

Note that you should also specify the DMBS in the export.

Before you write a macro or CALL EXECUTE you need to have your base program working first, and then you make the CALL EXECUTE match that format.

This is the code you need to get working first - what's the delimiter there? I don't think it should be the symbol you have listed...

filename tmp pipe 'dir "C:\Users\Documents\incidentes" /s /b';

data test;
	infile tmp dlm="¬";

	length buff $2000;
	input buff $;
	fname=scan(buff, -1 , "\");

run;

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 16:34:21 GMT

PS. Your pipe code works fine for me, are you sure your path is correct?

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 16:40:15 GMT

Now in the new code the result is:

NOTE: The infile TMP is:
      Unnamed Pipe Access Device,
      PROCESS=dir "C:\Users\Documents\incidentes" /s /b,
      RECFM=V,LRECL=32767

I am sure that the pass is correct. I have a program with an import file(xlsx) from that folder.

thanks for your answer

EDIT: When i try to delete the path for C:\Users\Documents\ the result is other folders and not excel type

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 16:43:20 GMT

So if you copy and paste that path into a Windows pane it goes to the folder?

This macro lists CSV files, but you can try changing it for XLSX and see if it works. Or at least the portion about getting the file names.

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 16:47:08 GMT

I know that link. I try it 30 minutos ago. With no results.

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

.......

c:\Users\Documents\incidentes cannot be open.

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 16:49:14 GMT

Is SAS running on a server or your desktop?

Either way it looks like it doesn't have access to that location. There isn't anything wrong with either code. The macro doesn't required XCMD so it's not that, so the only thing that it looks like to me is a path issue.

If you run the DIR command outside of SAS does it work?

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 17:05:34 GMT

SAS is running on server outside.

I am using sas enterprise guide. The folder C is on a server, but i also tried on desktop H: and it doesn't work.

I am confuse because i can programming with one file xlsx with the same path and works fine for IMPORT data from de excel.

Thanks your help

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 17:19:33 GMT

Can you post the log from that successful PROC IMPORT step?

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 17:46:31 GMT

I am not using code. I am using the functions of enterprise:

28         DATA WORK.TTag_alterado;
29             LENGTH
30                 Data             $ 23
31                 Descritivo       $ 84 ;
32             FORMAT
33                 Data             $CHAR23.
34                 Descritivo       $CHAR84. ;
35             INFORMAT
36                 Data             $CHAR23.
37                 Descritivo       $CHAR84. ;
38             INFILE 'F:\saswork\*********************
39                 LRECL=108
40                 ENCODING="WLATIN1"
41                 TERMSTR=CRLF
42                 DLM='7F'x
43                 MISSOVER
44                 DSD ;
45             INPUT
46                 Data             : $CHAR23.
47                 Descritivo       : $CHAR84. ;
48         RUN;

NOTE: The infile 'F:\saswork\*********************is:
      Filename='F:\saswork\*****************,
      RECFM=V,LRECL=108,File Size (bytes)=52812,
      Last Modified=05Feb2018:15:32:56,
      Create Time=05Feb2018:15:32:56

NOTE: 490 records were read from the infile 'F:\saswork\*********
      The minimum record length was 1.
      The maximum record length was 108.
2                                                          The SAS System                             14:59 Monday, February 5, 2018

NOTE: The data set WORK.TTAG_ALTERADO has 490 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

I have the query of 490 observations

regards

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 17:49:09 GMT

Ah...that's a different path than you were using previously, F:\ , I assumed it was the same C:\ path.

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 17:59:54 GMT

It is not a different path. I don't know why SAS assume that path F:

I change the location Analises_incidentes to incidentes because of the problem '_' under score.

And with this method i can import one Excel file, if you see the figure

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 18:00:19 GMT

Did you write that code or did you use a Task to import the data?

What's in the Zip file? Why should I download it? Please just include it in the posts, you can use a spoiler entry if you think it gets too long.

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 18:02:24 GMT

Yes i use task. That task is in figure(zip file). SAS community don't accept png or jpeg files.

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 18:12:11 GMT

JPGs are fine, insert them using the Photos option.

The Import Task is a different method, it can access data locally which you can't do via code. I'm assuming you're using EG or Studio? For this to work you'll need to move your files first to the server and then run the code you were trying to run with a path to the server folder.

Re: Import multiple Excel files(xlsx)

Aleixo — Mon, 05 Feb 2018 18:14:31 GMT

I am using EG.

Can i use task to import multiple Excel files? Or it is only by code(macro)?

Re: Import multiple Excel files(xlsx)

Reeza — Mon, 05 Feb 2018 18:16:00 GMT

@Aleixo wrote:

I am using EG.

Can i use task to import multiple Excel files? Or it is only by code(macro)?

Only via code, not necessarily a macro but not via a task as far as I'm aware. Chris Hemidinger wrote a post on this here or his blog in the last month or so.

Re: Import multiple Excel files(xlsx)

Aleixo — Tue, 06 Feb 2018 14:02:36 GMT

I change the location of my files. I put them on the server and run successful.

Thanks for your time xD