import a crazy csv file

[melassiri]

I tried to import a file in csv format in sas but not without problem.
First I did it normally like:
proc import datafile=reffile
out=test_Eems
dbms=csv
replace;
getnames=yes;
run;
without success and I saw in the log that there could be talk of delimiter ';'.
So I added delimiter ';'.

proc import datafile=reffile
out=test_Eems
dbms=csv
replace;
delimiter=";" ;
getnames=yes;
run;

I get a better result but not always correct because a column (called Bericht) contains all kinds of characters also the semicolon.
Of course I get an error in the log.
What surprises me is that I could open the same file without problem in Excel or a google sheet but not in Sas.
I have converted the same file to an excel file using excel and I could easily read and open it in sas.

My question to you is if I could open the file in Excel and google sheet then it should also work in Sas.
I would like your help with that.
Below are the file that I could not import in csv format but in excel and the log that I got when I tried with csv format

[Tom]

I suspect your file as embedded LF characters?

Try telling SAS to use TERMSTR=CRLF when reading the file.

data test;
  infile reffile dsd dlm=';' firstobs=2 termstr=crlf truncover;
  input (var1-var12) (:$200.);
run;

If you want to continue to use PROC IMPORT to make GUESSES about what the variables are and what types of data the variables have then you can add the TERMSTR= option to the FILENAME statement that defined the fileref REFFILE you used in your code:

filename reffile 'physical filename' termstr=crlf ;

If I use CSV2DS macro to guess how to read the file it defines the variables this way:

1089 +data test2;
1090 +  infile CSV termstr=crlf dlm=';' dsd truncover firstobs=2 ;
1091 +  length Datum 8 Url $341 Sentiment $8 Type $7 Discussie_lengte 8 Views 8
1092 +    Auteur $81 Volgers 8 Bron $9 Titel $172 Bericht $9010 Labels $1
1093 +  ;
1094 +  informat Datum anydtdtm. ;
1095 +  format Datum datetime19. ;
1096 +  label Discussie_lengte='Discussie lengte' ;
1097 +  input Datum -- Labels ;
1098 +run;

[JOL]

The best way to read this file into SAS is to use the Data Step.

data test;

infile "c:\temp\test_Eems.csv" dlm=';' dsd missover firstobs=2;

input Datnum : $15.  Url : $100. Sentiment : $10. Type : $10.

Discussie_Lengte : 8. Views : 8.  Auteur : $25.  Volgers : 8.

Bron : $15. Title : $200. Bericht : $300.  Labels : $10.;

run;

proc print data=test;

run;

The above method uses the Modified List Input method SAS Help Center: INPUT Statement: List

[Patrick]

Using the SAS EG import wizard to generate data step code appears to work.

Below the generated code with some manual changes applied (like longer character variables so they hopefully are also sufficiently long for all your data and not only the sample).

DATA WORK.test_Eems;
    LENGTH
        Datum              8
        Url              $ 500
        Sentiment        $ 8
        Type             $ 7
        Discussie_lengte   8
        Views              8
        Auteur           $ 250
        Volgers            8
        Bron             $ 9
        Titel            $ 250
        Bericht          $ 1500
        Labels           $ 10 ;
    LABEL
        Discussie_lengte = "Discussie lengte" ;
    FORMAT
        Datum            date9.
        Url              $500.
        Sentiment        $8.
        Type             $7.
        Discussie_lengte best32.
        Views            best32.
        Auteur           $250.
        Volgers          best32.
        Bron             $9.
        Titel            $250.
        Bericht          $1500.
        Labels           $10. ;
    INFORMAT
        Datum            ddmmyy20.
        Url              $500.
        Sentiment        $8.
        Type             $7.
        Discussie_lengte best32.
        Views            best32.
        Auteur           $250.
        Volgers          best32.
        Bron             $9.
        Titel            $250.
        Bericht          $1500.
        Labels           $10. ;
    INFILE 'C:\temp\test_Eems.csv'
        LRECL=30000
        ENCODING="UTF-8"
        TERMSTR=CRLF
        DLM=';'
        truncover
        DSD ;
    INPUT
        Datum            
        Url              
        Sentiment        
        Type             
        Discussie_lengte 
        Views            
        Auteur           
        Volgers          
        Bron             
        Titel            
        Bericht          
        Labels   
        ; 
RUN;