topic Re: Import files to saswith the possibility of selecting them before the import in SAS Enterprise Guide

Import files to saswith the possibility of selecting them before the import

Uzi — Thu, 22 Mar 2018 13:10:10 GMT

Dear experts,

First of all, I'm not a great expert on the program. I am an actuary who tries to improve processes through Sas.
explanation:
I am working with another program called Moses and for shortening running times I split the run into several computers .
The result is multiple .csv files in the local of multiple computers.
I have to group them back into one file (apend) and then continue to work with a unified aggregat file.
I can determine the names according to my convenience under the constraint of Quarter and Year.

The problem:
There are several runs in each of the folders and I have to choose each time a specific run.
This means that each time I have to see the file names before importing and then choose who to import and group according to the conditions I set.
Before that I used Excel to consolidate the files and I could use a "flag"\"pointer" for the run I wanted to consolidate.
Hope I've been able to explain myself properly.
Thank you in advance for your help!

That's what I've collected so far from the forum but I get an error while creating files.

%let path1=\\ayl-celerra-new\work\li\Moses\2017\Output\Check\;

data filelist;

keep i dnum filename;

length fref $8 filename $300;

rc = filename(fref,"&path1");

if rc = 0 then

do;

did = dopen(fref);

rc = filename(fref);

end;

else

do;

length msg $200.;

msg = sysmsg();

put msg=;

did = .;

end;

if did <= 0

then

putlog 'ERR' 'OR: Unable to open directory.';

dnum = dnum(did);

do i = 1 to dnum;

filename = dread(did, i);

/* If this entry is a file, then output. */

fid = mopen(did, filename);

if fid > 0

then

output;

end;

rc = dclose(did);

run;

data one;

set filelist;

infile dummy filevar=fil2read end=done;

do while(not done);

input a b c;

output;

end;

run;

and I get this ERROR:

60 data one;

61 set filelist;

62 infile dummy filevar=fil2read end=done;

63 do while(not done);

64 input a b c;

65 output;

66 end;

67 run;

ERROR: Invalid physical name.

filename=EV0_IF2016Q4_16f_BE16.csv dnum=3 i=1 fil2read= done=0 a=. b=. c=. _ERROR_=1 _N_=1

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 1 observations read from the data set WORK.FILELIST.

WARNING: The data set WORK.ONE may be incomplete. When this step was stopped there were 0 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.01 seconds

memory 1053.46k

OS Memory 23132.00k

Timestamp 03/22/2018 02:31:40 PM

Step Count 3 Switch Count 57

Re: Import files to saswith the possibility of selecting them before the import

SuryaKiran — Thu, 22 Mar 2018 13:19:49 GMT

Hi @Uzi,

The log clearly says that "\\ayl-celerra-new\work\li\Moses\2017\Output\Check\" this path is not known to SAS. Looks like this is a network drive and not the path in Server where SAS is installed. Make sure your files are in a location where SAS can access them.

Re: Import files to saswith the possibility of selecting them before the import

Uzi — Thu, 22 Mar 2018 13:26:03 GMT

Thanks for the quick response. I can see the list of files which means that the first part works but I can not import them.

Re: Import files to saswith the possibility of selecting them before the import

SuryaKiran — Thu, 22 Mar 2018 14:05:02 GMT

Check if this helps for you. Change the import for the files you have (In my case xlsx files)

%let Path=<Your path here>;
/* Read all the files that are in a specific folder */
FILENAME _folder_ "%bquote(&path.)";
data filenames(keep=memname);
FORMAT memname $100.;
  handle=dopen( '_folder_' );
  if handle > 0 then do;
    count=dnum(handle);
    do i=1 to count;
      memname=dread(handle,i);
      output filenames;
    end;
  end;
  rc=dclose(handle);
run;
filename _folder_ clear;

/* Count total files in the location and add all the file names into a macro */
PROC SQL noprint;
SELECT COUNT(*),memname INTO: Total_Files, : All_Files separated by "," FROM filenames;
QUIT;

%PUT "Total Files:" &Total_Files "All Files:" &All_Files;


/* Close files if they are opened before importing into SAS. If they are not closed then you may get an error */
/* If you get error then try re-running it aganin */

%MACRO CLOSE_FIELS();
%DO I=1 %TO &Total_Files ;
%LET File=%SCAN("&All_Files",&i,",");
%let filrf=myfile;
%let rc=%sysfunc(filename(filrf,&path./&FILE.));
%let fid=%sysfunc(fopen(&filrf));
%if &fid > 0 %then
   %do;
      %let rc=%sysfunc(fread(&fid));
      %let rc=%sysfunc(fclose(&fid));
   %end;
%else
   %do;
      %put %sysfunc(sysmsg());
%end;
%let rc=%sysfunc(filename(filrf));
%END;
%MEND;
%CLOSE_FIELS;

/* Macro to import all the files from  path to SAS  */
/* If you get an error then re-run this code again. If the excel file is opened then you may get an error */

%MACRO IMPORT_FILES();
%DO I=1 %TO &Total_Files ;
%LET File=%SCAN("&All_Files",&i,",");
proc import 
  datafile="&path./&File."
  dbms=xlsx 
  out=GRIDWORK.TASK&i. 
  replace;
RUN;
%END;
%MEND IMPORT_FILES;
%IMPORT_FILES();

/* Append imported data into a single dataset */ 
%MACRO APPEND();
%DO I=2 %TO &Total_Files;
PROC APPEND BASE=GRIDWORK.Task1 DATA=GRIDWORK.Task&i. FORCE;
RUN;
%END;
%MEND APPEND;
%APPEND();

Re: Import files to saswith the possibility of selecting them before the import

Uzi — Fri, 23 Mar 2018 11:11:52 GMT

Thank you very much for your help.
When I run the code I receive an error message when the files are imported.
The list of files i can see-the program has been able to
fetch.
77 /* Macro to import all the files from path to SAS */

78 /* If you get an error then re-run this code again. If the excel file is opened then you may get an error */

79

80 %MACRO IMPORT_FILES();

81 %DO I=1 %TO &Total_Files ;

82 %LET File=%SCAN("&All_Files",&i,",");

83 proc import

84 datafile="&path./&File."

85 dbms=csv

86 out=GRIDWORK.TASK&i.

3 The SAS System 14:01 Friday, March 23, 2018

87 replace;

88 RUN;

89 %END;

90 %MEND IMPORT_FILES;

91 %IMPORT_FILES();

ERROR: Library name is not assigned.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 146.75k

OS Memory 24156.00k

Timestamp 03/23/2018 02:01:29 PM

Step Count 18 Switch Count 20

ERROR: Library name is not assigned.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 146.75k

OS Memory 24156.00k

Timestamp 03/23/2018 02:01:29 PM

Step Count 19 Switch Count 20

Re: Import files to saswith the possibility of selecting them before the import

SuryaKiran — Fri, 23 Mar 2018 12:53:28 GMT

@Uzi ,

I have "GRIDWORK" library in my code as my temporary work library. You may need to change that to "WORK". The log says "GRIDWORK" library is not assigned.

Re: Import files to saswith the possibility of selecting them before the import

Uzi — Fri, 23 Mar 2018 13:35:51 GMT

You're right!
Thank you very much.
You really helped me and I really appreciate it. 🙂

Re: Import files to saswith the possibility of selecting them before the import

Tom — Fri, 23 Mar 2018 13:36:10 GMT

Make sure to use the {i} or Running man ICON to paste your log and/or code.

Is the problem that you searched for files in a directory and then tried to open the file without prefixing the directory name?

Re: Import files to saswith the possibility of selecting them before the import

Uzi — Mon, 26 Mar 2018 13:58:30 GMT

Hi Tom,
I'm not sure I understood the question ..

Suryakiran, i have another question:
what should be done to this code if you want to create a macro that can read data from five different folders in a different computers in a loop

Re: Import files to saswith the possibility of selecting them before the import

Tom — Mon, 26 Mar 2018 14:52:53 GMT

@Uzi wrote:
Hi Tom,
I'm not sure I understood the question ..

Suryakiran, i have another question:
what should be done to this code if you want to create a macro that can read data from five different folders in a different computers in a loop

Your error message is listing a filename that is not a fully qualified filename. It does not tell SAS what directory to look in.

filename=EV0_IF2016Q4_16f_BE16.csv

If you are on Windows then the filename should look something like C:\somedir1\anotherdir2\EV0_IF2016Q4_16f_BE16.csv. If your SAS session is running on Unix then it should look like /somedir1/anotherdir2/EV0_IF2016Q4_16f_BE16.csv. Also on Unix the filenames are case sensitive.

Re: Import files to saswith the possibility of selecting them before the import

Uzi — Sun, 22 Apr 2018 14:37:23 GMT

Hey Experts,
after a few attempts I realized I had a problem.My data is changing. For example, a policy number can appear once a number and once a number and a letter.
The proc import step checks for the first few lines(20) and then determines the format of the field. In my case, the program encountered only a number and therefore determined numerical while it could also be a charcter.
Is it possible to update SuryKirn's program so it will know determine the format of the fields correctly?
A reminder:
I need to select only a few files (a specific files) from the folder (not all the files in the folder) and apend them in to one file.
Thanks in advance for the help and forgiveness for my ignorance in sas.

Re: Import files to saswith the possibility of selecting them before the import

Tom — Sun, 22 Apr 2018 15:39:36 GMT

@Uzi wrote:
Hey Experts,
after a few attempts I realized I had a problem.My data is changing. For example, a policy number can appear once a number and once a number and a letter.
The proc import step checks for the first few lines(20) and then determines the format of the field. In my case, the program encountered only a number and therefore determined numerical while it could also be a charcter.
Is it possible to update SuryKirn's program so it will know determine the format of the fields correctly?
A reminder:
I need to select only a few files (a specific files) from the folder (not all the files in the folder) and apend them in to one file.
Thanks in advance for the help and forgiveness for my ignorance in sas.

For CSV files you can change the GUESSINGROWS option, but that won't solve the problem if the particular subset of data in that CSV file happens to only have ID codes that look like numbers. If you don't want SAS to guess at how to define your variables then do not use PROC IMPORT to read your files. Instead write you own data step to read the files and you will have complete control over how the variables are defined.