Reading a CSV file

[mediaeval]

I'm reading a CSV file into a SAS dataset using Infile. In the Log, the file location is confirmed. Is it possible to print other information regarding that CSV file to the Log, such as file size, date created, file owner etc?

[Tushar]

Hi mediaeval,

In SAS 9.2, the FINFO function (in conjunction with the FOPEN and FCLOSE functions) retrieves system information from a file. You provide the file ID (that is assigned when the file is opened with the FOPEN function) and the name of the file information item that you want to retrieve, as shown in the following syntax:

FINFO(file-id,info-item)

The available information items (or, attributes) depend on the operating environment and the SAS release. If you do not know the available attributes, you can use the FOPTNUM and FOPTNAME functions to identify those attributes for use with the FINFO function.

The following example uses all five functions (FOPEN, FOPTNUM, FOPTNAME, FINFO, and FCLOSE) to retrieve available attributes and their values. Of course, you can simplify the program if you already know which attributes (for example, File Size(bytes) or Last Modified) that you want to retrieve.

data info;

   length infoname infoval $60;

   drop rc fid infonum i close;

   rc=filename("abc","c:\temp\mon3.dat");

   fid=fopen("abc");

   infonum=foptnum(fid);

   do i=1 to infonum;

      infoname=foptname(fid,i);

      infoval=finfo(fid,infoname);

      output;

   end;

   close=fclose(fid);

run;

proc print data=info;

   title1 "Attributes obtained for a file";

run;

In a Windows operating environment, the output appears as follows:

infoname             infoval

Filename             c:\temp\mon3.dat

RECFM                V

LRECL                256

File Size (bytes)    332

Last Modified        05May2009:10:44:23

Create Time          04May2009:17:04:22

For SAS 9.1.3 only fewer Attribute available. Replace thisdat file with your csv and try.

Regards,

Tushar J.

[mediaeval]

Thanks Tushar.

I have SAS 9.1.3. When I run the above code, only three information items are returned, the Filename, the RECFM and the LRECL, so I think this method will not work, since what I want is the File Size and when the file was created/modified, but thank you.

Joe

[Vince28_Statcan]

You can go around this issue if you have some knowledge of system commands using call system. I'm not familiar enough with it to specify for a single file but here is an example of a code a colleague made to issue a command that reads all files/folders in a directory and writes it to a temporary file (which he reads in a subsequent step to retrieve the relevant information).

data _null_;

call system("dir /s /q /T:c /4 /-c " "c:\dirtoread" " >c:\dirtowrite\filetowrite.txt" );

run;

Here is an example of a single line output in filetowrite.txt (starting on line 5)

28/05/2013  10:17 AM                    4049 STATCAN\martvin           Exploration.sas

This should allow you to get file size (4049), creation date  (28/05/2013 - /T:c option for creation date, /T:w option for last time written) and user who created it (STATCAN\martvin)

Edits:

*you may need to add options noxwait; before the data step. This could also be done in a X command line instead of call system so as to avoid the data _null_; run; syntax.

*Decent source to help you target your file better (xcommands basics) Dir - list files and folders | SS64.com

*You can actually get the information only for a file by using full file extension instead of just its root folder. The produced file is still the x command log file so the dataline starts on line 5 but that's easy to go around with INFILE statement options property.

After some iterations here's a way to get command log relevant line into your sas log:

options noxwait;

X "dir /s /q /T:c /4 /-c " "c:\dirtoread" " >c:\temp.txt";

data _null_;

     infile 'c:\temp.txt' pad encoding=pcoem850 lrecl=200 firstobs=6 obs=6;

     input @1 fileinformation $200.;

     put fileinformation;

run;

Vincent

[Kanna]

Step 1.
*/

Options NOXWAIT;  /*Close command window without interacting / waiting for type in "EXIT" and press Enter*/

X "DIR /A:-D  /T:C c:\test\myFile.CSV > c:\test\myDirC.txt";  /* List file creation date  /T:C  */
X "DIR /A:-D  /T:W c:\test\myFile.CSV > c:\test\myDirW.txt";  /* List date Last Written   /T:W  */

Options XWAIT;  /*Reset the environment to default setting */

/*
  To get the full folder contents,  use C:\test\*.*
   To get all CSV files,  use C:\test\*.CSV
*/

/*Step 2 - read the list created in step one */

Data DIRlist (drop = firstchr);
infile "c:\test\myDirW.txt" truncover;
  input firstchr $1. @ ;
  if firstchr ne "" then do;   /*Reed in only related information */
   input
    @01 fildat mmddyy10.  /*File date  */
    @40 filname $100.;   /*File name  */
   end;
  format fildat mmddyy10.;
  if firstchr ne "";            /*Keep only the useful line(s) */
  run;

/* I hope this helps  */

[Tom]

If you are allowed to use operating system commands then use that to get information on the file.  If you just want it into the LOG a simple DATA _NULL_ step will do.  Let's assume that you have the name of the file in the macro variable MYFILE.

data _null_;

* Windows ;

* infile "dir &myfile" pipe ;

* Unix ;

  infile "ls -ld &myfile" pipe ;

  input;

  put _infile_;

run;

[mediaeval]

Hi Tom,

I'm using Windows. This method gives me what I need, and it works when there are no spaces in the directory name, such as

%Let myfile = Y:\Excel\Instructions.pdf;

But for filenames such as

%Let myfile = Y:\Valuation Development\Procedural Data.xls;

It generates an error in the log,

   Stderr output:

   File Not Found

The file does exist. It doesn't like the spaces, I think.

Thanks

Joe

[Tom]

infile %sysfunc(quote(dir "&myfile")) pipe ;