SAS proc import .csv files missing column issue

Question

I am trying to import multiple .csv files into SAS. Those csv files are 12 months' hourly temperature data each column represent date and time. For instance, column for January file is: 20120101 00-20120131 23. Since the length of month differs the column numbers differ accordingly.

Using following codes to import data,

 proc import out=air.air&year
 datafile="year\hourly*.csv" dbms=csv replace;
 getnames=yes;
 DATAROW=2; 
 run;

I only got date time up to day 29. I guess the reading sequence matters so I tried to reorder the file name to make the January the first file but still failed to read in all columns.

It's not easy to specific the files names one by one given about 20 years monthly hourly data involved. Does anyone know in which order SAS reads files within one folder?

Really appreciated if someone could point me one direction of dealing similar task appreciated.

Thanks!

Tom Tom · Accepted Answer · 2015-09-09T21:05:35

If you know the structure there is no need to use PROC IMPORT to read CSV files. Just read them directly with a DATA STEP. For example this should work if all of the values are numbers and there is one header row at the top of each file that needs to be skipped. You should be able to parse the year and month out of the FILENAME. If the columns are really from hour 0 for day 1 to hour 23 for the last day in the month then just use DO loop to increment the DAY and HOUR variables.

data AIR ;
  length filename filevar $200 ;     
  infile "&path\hourly*.csv" dsd truncover length=ll column=cc filename=filevar;
  input @;
  filename=filevar;
  if filename ne lag(filename) then do;
     input / @ ;
     row=0;
  end;
  row+1;
  do day=1 to 31 while (cc < ll) ;
    do hour=0 to 23 while (cc < ll) ;
      input value @ ;
      output;
    end;
  end;
run;

SAS proc import .csv files missing column issue

1 Answers