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
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;
The best way to read this file into SAS is to use the Data Step.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.