Importing a delimited file into cas

[AmitSri]

Hi,

I am trying to import a csv file which is seperated by ";". When I run this query

proc casutil;
   load casdata="20180226_all_markets_export.csv"   incaslib="dbdata"                     
        importoptions=(filetype="csv" getnames="true")
               casout="20180226_all_markets_export" outcaslib="MYCASLIB" promote;
run;

I am getting the error as:

ERROR: Row data has too many columns.

ERROR: The action stopped due to errors.

Could anyone suggest on resolving this error.

Also is there any option to specify dlm= option in cas? Many Thanks!!

[RW9]

Then it is not a CSV file.  Comma Separated Variable file = CSV.  Yours is a delimited file.  

On your issue, what does the CSV look like?  How many columns does it have?  Does it have correct line ends - i.e. if the file is pulled off Unix then it might not have the correct termstr to read in correctly.  Can't really say without seeing the file.

[AmitSri]

Hi, It was actually a .rpt file. So, I just renamed it with .csv and trying to load it. The file is just seperated with a semicolon. However my script is now running when I placed a diletype argument delimiter=";". Since its a huge file of 120GB, I will post the status after its completion/failure.

Thanks!!

[AmitSri]

Oops. Again same error. The file has 26 columns and the values are seperated by a ; and the observation ends with \n. Any other arguments to be passed ion the query? Thanks!!

[RW9]

You would need to read it manually with a datastep as \n is not a valid termstr.  What is likely happening is that SAS cannot find the end of line, and so is reading the whole file as one string.  What is an rpt file?  Can you not get the original data, its far easier to work with real data than trying to convert output files back to data.  Alternatively you could open with notepad or some similar, and find / replace all occurences of "\n" with '0a'x which I think (you will need to check as am in training), en of line character, or '0a'x and '0d'x.

[AmitSri]

But, Its a huge file of 120Gb. So, unable to do that.Thanks!!

[RW9]

Go back to source then and get actual data, don't use output files.  I am afraid there isn't an ad-hoc end of line character for \n:

http://support.sas.com/kb/14/178.html

The program which renders that file (e.g. Word) may know that \n is newline, but for files you would only have unix or windows end of line.