topic Re: seeking help on importing CSV file in SAS Procedures

seeking help on importing CSV file

Mariaho — Wed, 19 Aug 2015 21:46:37 GMT

Hello,

I have the following files which are generated daily from another system which is date and time stamped.

I have a SAS process which is required to read in these daily files.

I have set up a macro variable so that it creates the date with the last business day %let file_date_m = &file_date_yr.&file_date_mth.&file_date_day.;

As for the time, I would not know when it will be generated, my question is, how can I find the correct file for SAS to read in and can the time part be omitted ?

I would like to set up a macro variable like this (%let cdsfilename = "CDSALL0E_&file_date_m._&time.csv"n;) so that I can read in the correct file depending on the business day.

These files are being read in via PC Files Server.

Examples of the filenames:

CDSALL_20150810_081411.csv

CDSALL_20150811_073451.csv

CDSALL_20150812_072431.csv

CDSALL_20150813_080519.csv

CDSALL_20150814_075526.csv

CDSALL_20150817_080139.csv

CDSALL_20150818_071141.csv

CDSALL_20150819_082730.csv

Thank you.

Re: seeking help on importing CSV file

ballardw — Thu, 20 Aug 2015 16:40:16 GMT

Execution time may be your bigger concern.

You can create a fileref with the name and date stem and ignore the time part using OS filesystem wildcards. In window.

Filename Input "c:\path\CDSALL_20150810_*.csv"; or use in an infile statement directly.

I am a tad concerned about this:

"CDSALL0E_&file_date_m._&time.csv"n

as you show an _m that does not appear in your input file name examples. Also the "n makes me think you are attempting to make a SAS variable name as most external file operations.

If you show some of the code how your are attempting to use the filename macro variable we might be able to provide some additional details.

Re: seeking help on importing CSV file

data_null__ — Thu, 20 Aug 2015 17:19:17 GMT

You may be able to use a wild card in a FILEREF. Consider the following example which create some files using the names you show and then reads the one from TODAY()-2.

data _null_; 
   input filename $64.; 
   filevar=catx('/','~',filename);
   file dummy filevar=filevar dsd; 
   do i = 1 to 3; 
      put i filevar;
      end; 
   cards; 
CDSALL_20150810_081411.csv
CDSALL_20150811_073451.csv
CDSALL_20150812_072431.csv
CDSALL_20150813_080519.csv
CDSALL_20150814_075526.csv
CDSALL_20150817_080139.csv
CDSALL_20150818_071141.csv
CDSALL_20150819_082730.csv
;;;;
   run; 

%let target=%sysfunc(intnx(day,%sysfunc(today()),-2),yymmddn8);
%put NOTE: &=target;
filename FT45F001 "~/CDSALL_&target.*.csv"; 
data cds;
   length filename $128; 
   infile FT45F001 filename=filename dsd; 
   file=filename;
   input i filevar:$64.; 
   run; 
proc print; 
   run;

Re: seeking help on importing CSV file

Tom — Thu, 20 Aug 2015 18:03:50 GMT

Why are you using PC FILES SERVER? Does that mean that the actually machine that SAS is running on cannot see the files? Because there should not be any other reason you need it since you can read a CSV file with BASE/SAS data step.