BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aleixo
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

 

 

 

 

 

 

 

 

View solution in original post

17 REPLIES 17
Reeza
Super User

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;

 

Reeza
Super User

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

 

Aleixo
Quartz | Level 8

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

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.06 seconds
      cpu time            0.06 seconds

 

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

Reeza
Super User

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&docset...

 

Aleixo
Quartz | Level 8

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.

 

Reeza
Super User

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?

 

 

Aleixo
Quartz | Level 8

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

Reeza
Super User

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

Aleixo
Quartz | Level 8

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

Reeza
Super User

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

Aleixo
Quartz | Level 8

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

 

Reeza
Super User

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. 

Aleixo
Quartz | Level 8

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

Reeza
Super User

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.

 

 

 

 

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 3449 views
  • 1 like
  • 2 in conversation