@xinyao2019 wrote:
When i saved the csv file to xlsx, it can be read to sas.
should i just do it?
That is a viable solution. Excel will treat recognize that line breaks embedded inside quotes are part of the value and not an indication of the start of a new observation. But you need to take care when reading a CSV file into Excel as it will improperly transform text strings that happen to look like numbers or dates. So don't just open the CSV file from a list of files and let Excel autoload it for you. Instead if you open Excel first and then ask it to open the file you are usually given the chance to to set the data type for each column.
Something strange starts to happen at line 1387. You need to inspect what is unusual over there.
Change your code slightly and paste the output.
DATA _NULL_; INFILE "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" DELIMITER=","; IF _N_=1387 THEN PUTLOG _ALL_; RUN;
50722 DATA _NULL_;
50723 INFILE "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv"
50723! DELIMITER=",";
50724 IF _N_=1387 THEN PUTLOG _ALL_;
50725 RUN;
NOTE: The infile "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" is:
Filename=Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv,
RECFM=V,LRECL=32767,File Size (bytes)=44058484,
Last Modified=24Jun2019:08:31:06,
Create Time=07Jan2019:09:11:01
NOTE: 0 records were read from the infile
"Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv".
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
Run the Simpler version suggested by Tom here (https://communities.sas.com/t5/SAS-Programming/proc-import-csv-file-errors/m-p/569190#M160370) ..it should work
Thank you so much to remind me the contents in the log. i did not realized it.
this is what i got after i ran the simple version.
DATA _NULL_;
50716 INFILE "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv"
50717 firstobs=1386 obs=1388
50718 ;
50719 input;
50720 list;
50721 RUN;
NOTE: The infile "Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv" is:
Filename=Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv,
RECFM=V,LRECL=32767,File Size (bytes)=44058484,
Last Modified=24Jun2019:08:31:06,
Create Time=07Jan2019:09:11:01
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----
1386 2019001462,aaaaaaaa,bbbbbb,sssssss, ,F,1,00,00,01/01/1900,01,01,1900,555-55-5555,01/
00 01/2019,50,20,01/01/2019,1,1,2019,A,10:30,A,aaaaaaa pppppppppp,99999, ,, ,99,XX,PHIL
169 iiiiiii,RP,40, ,0,2915 S ffffffffff ST ,sssssss,63e3,Kkkk,1701,17,33,717,WASHINGTON,
253 48,UNITED STATES,99999,0,810,DECEDENT'S HOME,N,M,0,FLORENDO,Y,INOVEJAS, ,6,9,0,0,SUP
337 PLY ROOM,0,IIIIIII HOSPITAL,PPPPPPPP, ,SSSSSSS, ,TTTTTTT, ,CCCC,FFFFFFFF,Y,NNNNNNNN,
421 ,"2222 S FFFFFFFFFF ST, EEEEEEE, WA, 99999",HHHHHHH,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,
505 , , ,, ,, , ,420, , ,,,,,, , ,,,,, ,,6,13,7,Y,N,N,N,N, ,100,,2915 S FFFFFFFFFF ST ,S
589 SSSSSS,63e3,Y,KKKK,1701,17,33,717,WASHINGTON,WA,48,UNITED STATES,US,99999,,,,,,,,,,0
673 ,0,0,0, ,,Y,50,KKKKKKKKK,KKKKKK,KKKKKKKKK KKKKKK,1111,EEEEEEEEE-WASHELLI NNNNNNN HO
757 AA,AAAAAAA,WASHINGTON,99999,E,AAAAAAAAAA,01/01/1900,1,01,2019,9999,AAAAAAAAA AAAAAAA
841 L AAAA,AAAAAAA,WASHINGTON,UNITED STATES,AAAAAAA,AAAAAAA,"AAAAAAA A. AAAAAAA, JR, MD"
925 ,6044 AAA JR AAA AAA. 111 ,AAAAAAA,WA,99999,MD11111111,1,01/10/1900, , ,9, ,N,1,2,3,
1009 4,,,,,,,,,,,,,,,,,1,1,1,1,,,,,,,,,,,,,,,,,A419,I48,I499,J969, , , , , , , , , , , ,,
1093 ,,,, , , , , , , , , , , , , , ,,,,,,,I48, ,2019,00400001,I48,A419,J969, , , , , , ,
1177 , , , , , ,,,,,,I48,AAAAAA AAAAAA,AAAAAA AAAAAAAAAAAAA,AAAAAAA ARRHYTHMIA,RESPIRATOR
1261 Y FAILURE,SEVERE AAAAAA,AAAAAA FIBRILLATION,AAAAAAA ARRHYTHMIA,RESPIRATORY AAAAAAA,
1345 , ,1 HR,1 HR,1 HR,1 HR,1 HR,1 HR,1 HR,1 HR,N,N,N,N,N,X,8,0,U,0,0,N,N, ,,,, ,99:99, ,
1429 M, , , , ,0,0, ,, ,, ,U, , , , ,AAAAA AAAAA, ,01/14/1900, ,01/14/1900,0,298366,"QR 0
1513 2/20/2019 1521
1387 DA19-00512",,,1,20190304,1,2019-01-16T02:06:20Z,0,1,N,N,N,N,Y 61
1388 2019001463,AAAA,AAAAAAAA,AAAA, ,F,1,90,90,08/12/1928,8,12,1928,269-24-9932,01/14/201
85 9,239,20,01/13/2019,1,01,1900,A,04:14,AAAAAAA,99999, ,,AAAA,36,AA,UNITED STATES,US
169 ,40, ,0, ,AAAAAAA,67e3,AAAAAAA,3201,32,63,732,WASHINGTON,00,UNITED STATES,00000,4,16
253 2,INPATIENT,N,W,0, , , , ,3,9,0,0,AAAAAAAAA,0,AAA AAAA,AAAAAAAA,G,AAAAAA, ,AAAAAAA,
337 ,AAAAAAAAA,AAAA, ,AAAAAAA, ,"20709 S. AAAAA RD. AAAAA, WA 00000",DAUGHTER,Y,N,N,N,
421 N,N,N,N,N,N,N,N,N,N,N, , , ,, ,, , ,100, , ,,,,,, , ,,,,, ,,1,10,1,Y,N,N,N,N, ,100,,
505 00000 S. AAAAA RD. ,AAAAAA,00000,N,AAAAAAA,3204,32,63,732,WASHINGTON,WA,48,UNITED ST
589 AAAA,US,00000,,,,,,,,,,0,0,0,0, ,,M,9,AAAAAAA,AAAAAA,AAAAAAA R AAAAAA,000 ,AAAAAA FU
673 AAAAA AAAAAA,AAAAAA,WASHINGTON,00000,R,AAAAAAA AAAAAAASTATE,01/22/2019,1,22,2019,9999,
757 AAAAAAA AAAAAAAA AAAA,AAAAAAA,AAAAAAAAAA,UNITED STATES,AAAAAA,AAAAAA,"AAAAAA L. AAAA
841 LY, DO",000 A. 8AA AAA AAAA: 9A AAAAAAAAAAAA ,AAAAAAA,WA,00000,AA00000000,3,01/14/2
925 019, , ,9, ,N,1, ,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,I64, , , , , , , , , , , ,
1009 , , ,,,,,, , , , , , , , , , , , , , ,,,,,,,I64, ,0000,00400001,I64, , , , , , , , ,
1093 , , , , , ,,,,,,I64,AAAAA AAAAAA AAAAAAAA AAAAAA AAAAAA, , , ,AAAAA AAAAAA AAAAAAAA
1177 AAAAAA AAAAAA, , , , , ,AAAA, , , ,AAAA, , , , , , , ,N,X,8,0,U,0,,N, , ,,,, ,99:99
1261 , , , , , , ,0,0, ,, ,, ,U, , , , ,AAAAAAA AAAAAA, ,01/00/0000,0100,01/00/0000,0,000
1345 704, ,,,1,20190131,1,2019-01-16T02:06:20Z,1,1,N,N,N,N,N 0000
NOTE: 3 records were read from the infile
"Y:\Datasets\Restricted\Death\Preliminary\deathfull2019pre.csv".
The minimum record length was 61.
The maximum record length was 1521.
NOTE: DATA statement used (Total process time):
real time 0.20 seconds
cpu time 0.03 seconds
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: