SAS Infile statement errors when trying to read in CSV file

Question

My data looks like the following (this is a really small subset). This is a CSV file that's actually column seperated, so it can be read quite easily in Excel.

ParentFlag CurrentBalanceF ValuationAmountO ValuationDateO ValuationDateC

PARENT 85481.49 145000 13/02/2004 30/04/2009

I'm trying to use the following code to import my data.

filename indata '&location.\AE&CO - inputs - 2014 09 30 Sept.csv';

Data treasury;
    Infile indata firstobs=2 dlm=" "
/*  delimiter=','*/
; 
/*  Length ext_acno $14.;*/
/*  informat original_val_dt date9. current_val_dt date9. ;*/
  input pcd_acno $ 1 ext_acno $ 2 loan_acno $ 3 acno $ 4 account_bal $ 5 trust_id $ 6 parentflag $ 7 account_bal_f 8 
        original_val_amt 9 original_val_dt 10 current_val_dt 11 original_val_type 12 
        current_val_type 13 indexed_ltv 14 original_ltv_wo_fees 15 latest_ltv 16 account_status_rbs $ 17 ;
;
run;

However, the log gives me errors and the data doesn't import properly. My data set has fields that only have one character visible (for example, the parentflag field above only has a 0).

I tried doing this using the import wizard, and it worked to a certain extent, but the log comes up with an "import unsuccessful" message, despite my table populating correctly...

Ideally I'd like to get the infile statement working because it feels like a sturdier substitute. For now, it's just not behaving and I've no idea why! Could someone help?

Joe Joe · Accepted Answer · 2014-12-15T15:45:51

You need to remove those numbers after the dollar signs.

input pcd_acno $ ext_acno $ loan_acno $ ... ;

The numbers are just confusing SAS, they don't serve any purpose. They make SAS think that you're trying to do some sort of column input, but you want list input here. Column means the one character long column, not what you're using it to mean; "CSV" means "Comma separated values", not "column".

IE:

ABCDE FGHIJ KLMNO

Column 5 in the above is "E", and column 9 is "H".

SAS Infile statement errors when trying to read in CSV file

1 Answers