Macro Import

[Ramin1]

Hello Expert, I want to upload some excel files to SAS. I want to use the Macro function for this.
My files look like
2000, 2001, 2002....2013
Each of this year I have January to December, 12 months
Each of these months I have lots of Securities. Each security have its security number. 
For instance, in the 2001 file, I have 12 files comprising of January to December, in January I have securities file name TCD_TRB109032.csv, TCD_TRB234090.csv ...

1. First I want to import those file into SAS
2. I want to include all of these files into one SAS file based on their date, time, and security ID

[PaigeMiller]

---

@Ramin1 wrote:

Hello Expert, I want to upload some excel files to SAS. I want to use the Macro function for this.
My files look like
2000, 2001, 2002....2013

---

Are these file names, or contents of the file, or something else?

Upload files to SAS? Do you mean create SAS data sets?

If you run PROC IMPORT one on such Excel file, and get that to work, can you show us the working code? That's what we would need to create a macro, if indeed a macro is needed.

[ChrisNZ]

---

I want to upload some excel files to SAS.
I have securities file name TCD_TRB109032.csv, TCD_TRB234090.csv 

---

So you have Excel files or CSV files? You'll need to detail your need a lot better if you want help.

[ChrisNZ]

You may not need a macro at all.

An infile statement using a wildcard can read all your files in one go. Look this up and ask questions if necessary. The infile statement also has options to keep track of what data comes from what file. Look at this too.

[Reeza]

Luckily for you, CSV are not Excel files, they are text files and that makes it much easier to automate this entire process.

Can you provide the code to read one file correctly? It should be a data step though, not a proc import. Data steps are faster, more accurate and will allow you to read all files in a single set of code.

1. Crawl through the directories and create a list of files that need to be imported
2. Import files
   1. All into one single file, with a record indicating which file (FILEVAR/FILENAME options)
   2. Into unique files saved to a common library (macro)

There's a rough example here for a macro but it will cause issues with this many files if you don't use a data step.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n0ctmldxf23ixtn1kqsoh5bsgmg8.htm

 If you don't use a data step what will happen is that you'll likely get a few files where the data types are not the same causing issues when you want to work with the files.

---

@Ramin1 wrote:

Hello Expert, I want to upload some excel files to SAS. I want to use the Macro function for this.
My files look like
2000, 2001, 2002....2013
Each of this year I have January to December, 12 months
Each of these months I have lots of Securities. Each security have its security number. 
For instance, in the 2001 file, I have 12 files comprising of January to December, in January I have securities file name TCD_TRB109032.csv, TCD_TRB234090.csv ...

1. First I want to import those file into SAS
2. I want to include all of these files into one SAS file based on their date, time, and security ID

---

[Reeza]

INFILE Example of reading multiple files
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n1rill4udj0tfun1fvce3j401plo.htm#n...

Another example
https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

https://communities.sas.com/t5/SAS-Programming/Macro-for-importing-several-csv-files/m-p/717369#M221...

[Ramin1]

I have comma-delimited files like as attached. First I want to import all these files but the problem is they do not have any headings. After importing, I want to merge all the files by security ID, date, and time.

For more clarity, I have different years, and each year I have 12 files for each month, and each month I have lots of Securities. The file path "C:\Users\User\Desktop\workspace\2003\2003_KES_01\TradeComposedDynamicLOB_TRB020703T16.csv"

Here, 2003 denotes the year, I have 2001,2002...2013; 2003_KES_01 denotes January 2003; and .csv portion denotes security name.

1. I want to import all these files to SAS.

2. I want to merge all these files by security ID, date, and time.

Thanks all of you for your comment.

[Tom]

It is trivial to read a delimited file. Note that your example file is not a CSV (comma separated values) file, but it looks like they are delimited. They are using semi-colon as the delimiter.

950   data _null_;
951     infile "c:\downloads\TradeComposedDynamicLOB_TRB020703T16.csv" obs=3 ;
952     input ;
953     list;
954   run;

NOTE: The infile "c:\downloads\TradeComposedDynamicLOB_TRB020703T16.csv" is:
      Filename=c:\downloads\TradeComposedDynamicLOB_TRB020703T16.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=364143,
      Last Modified=03May2021:14:43:13,
      Create Time=03May2021:14:43:12

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         200301020000017;02/01/2003;TLR;09:46:27;200301020000206;200301020000015;TRB020703T16;KES;79925;;1000
     101  00;79925000000;0;0;02/01/2003;;50.649999999999999;;0;0;0;0;0;0;0;Y;S;094645697;79688;79925;237.000;3
     201  100000;6400000;null; 220
2         200301020000025;02/01/2003;TLR;09:52:01;200301020000259;200301020000207;TRB020703T16;KES;79925;;2000
     101  00;159850000000;0;0;02/01/2003;;50.649999999999999;;0;0;0;0;0;0;0;Y;B;095243113;79704;79925;221.000;
     201  100000;6200000;null; 220
3         200301020000030;02/01/2003;TLR;09:54:19;200301020000272;200301020000207;TRB020703T16;KES;79925;;3000
     101  00;239775000000;0;0;02/01/2003;;50.649999999999999;;0;0;0;0;0;0;0;Y;S;095447984;79815;79925;110.000;
     201  600000;5900000;null; 220
NOTE: 3 records were read from the infile "c:\downloads\TradeComposedDynamicLOB_TRB020703T16.csv".
      The minimum record length was 220.
      The maximum record length was 220.

You will need to makeup you own names for the 35 variables.  You can just use generic names to start with until you figure out what is in the files.

Do you need to keep some or all of the original filename to tell the records apart? Or is that information already in the file? For example in the first three records in your example file I see a date field that is from the year 2003 and values of other variables that start with the letters TRB that are in the filename.  

Here is code to read the files and keep the filename just in case you need it.

data want;
  length filename fname $256 ;
  infile "c:\downloads\TradeComposedDynamic*.csv" dsd dlm=';' truncover filename=fname ;
  input (v1-v35) (:$35.);
  filename = fname;
run;

proc print data=want (obs=3);
  by filename;
  id v1;
run;

Results

You could try letting PROC IMPORT try to figure out what type of variable each column contains.  But PROC IMPORT is not really that smart.  In particular it would probably decide to make V1, V5, V6 and V12 as numbers since they only include digits.  But they are clearly NOT numbers and should be kept as character strings.  Also values like "null" in a numeric column will make PROC IMPORT think that the variable should be defined as character even if the non null values are numbers.

[Ramin1]

Thanks a lot Tom. I want to upload lots of files like this into SAS. Do you have any suggestions...

My file path looks like...

"C:\RA\Aktas\2003\2003_KES_01\TradeComposedDynamicLOB_TRB020703T16.csv"

Aktas is a folder which includes lot more folder name 2001, 2002...2013, in the above example file path 2003. And each of the folder contains lots of LOB file, I want to export all of them in SAS. Then I want all of these LOB file should be in one LOB file. For instance, I have 1000 LOB file and total of 37000 observations. I want to include all of these observations in one single LOB file in SAS.

[Tom · Accepted solution]

When reading files across multiple directories it is probably better to work from a list of file names instead of trying to use the * wildcard in the filename.

Since it looks like you are running on Window you could use the DIR command to get the filenames.

Example:

data files;
  infile 'dir /b/s c:\downloads\*.csv' pipe truncover ;
  input filename $256. ;
run;

Results:

c:\downloads\xlsx_type_test.csv
c:\downloads\zero.csv
c:\downloads\test\cdisc_data.csv
c:\downloads\test\labels.csv

If you cannot use PIPE there are methods to use SAS functions to read directory contents instead to get the list of filenames.  

Once you have the list of filenames use that to drive the reading.  So use the FILEVAR= option instead of FILENAME= option.  You will need to add a DO loop and OUTPUT statement so that all records from a single file are processed in one iteration over the list of filenames.

data want;
  set files;
  fname = filename ;
  infile csv filevar=fname dsd dlm=';' truncover end=eof;
  do while(not eof);
    input (v1-v35) (:$35.);
    output;
  end;
run;