BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
_maldini_
Barite | Level 11

In the past I have used PROC IMPORT simply as a way of generating the DATA step syntax for reading a dataset into SAS. In other words, I would run PROC IMPORT and then copy and paste the underlying DATA step syntax from the log, and then change LENGTH, INFORMATS, etc. as needed. It was a shortcut for the DATA step.

 

The underlying syntax no longer appears in the log. Am I missing something? Did this change recently? 

 

I'm runnning SAS University Edition. 

 

Thanks.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@_maldini_ The code I provided didn't work? If not, can you post the log? Also, try with LF/CRLF instead of CR 🙂

 

Filename myfile 'path to csv.csv' TERMSTR=CR;

Proc import out =want datafile=myfile DBMS=CSV replace; *rest of options;
Run;

View solution in original post

27 REPLIES 27
SASKiwi
PROC Star

I'm pretty sure you can only do this with delimited file imports. What type of file are you importing?

_maldini_
Barite | Level 11

Well...I'm trying to read-in a .csv file, but I'm getting the following error:

 

"Unable to sample external file, no data in first 5 records. ERROR: Import unsuccessful. See SAS Log for details."

 

PROC IMPORT DATAFILE="/folders/myshortcuts/NEISS_Cell_Phone_Project/NEISS_2002_2015.csv"
    OUT=NEISS_2002_2015
    DBMS=csv
    replace;
    getnames=yes;
RUN;

 

There IS data in the first 5 rows. Including when looking at the file in a text editor. 

 

As a work around, I tried importing an Excel file. I was then going to take the syntax from the log to write my own DATA step to apply LENGTH, INFORMAT, etc.

 

 

 

 

Ksharp
Super User
Try Adding guessingrows=32767 option .
_maldini_
Barite | Level 11

I saw your prior post (2014?) on this and tried that approach. No luck. Same error message.

 

"Unable to sample external file, no data in first 5 records."

Reeza
Super User

99% sure TERMSTR is the solution.

Declare a fileref for your file with with the option and then use that with Proc import. 

 

I answered this within the last week...

 

Filename myfile 'path to csv.csv' TERMSTR=CR;

Proc import out =want datafile=myfile DBMS=CSV replace; *rest of options;
Run;

 

Reeza
Super User

SAS UE is a UNIX install. This seems to come up when the end of line character is different than expected. Try setting the TERMSTR OPTION. The values are CRLF/LF/CR. 

 

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

_maldini_
Barite | Level 11

I've used this option in a DATA step, but not a PROC IMPORT. Where exactly does it go?

 

PROC IMPORT DATAFILE="/folders/myshortcuts/NEISS_Cell_Phone_Project/NEISS_2002_2015.csv" TERMSTR=CR
_______
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,
_DEBUG_.
Tom
Super User Tom
Super User

Ask SAS to show you want is in the file. For example this simple data step will write the first 5 rows to the log. And if any line has non-printable charactrers you will see the hex codes for the characters printed under them.

 

data _null_;
  infile "/folders/myshortcuts/NEISS_Cell_Phone_Project/NEISS_2002_2015.csv" obs=5;
  input;
  list;
run;
_maldini_
Barite | Level 11

This seems to read in the entire dataset as 1 row...Hmm...

 

NOTE: 1 record was read from the infile "/folders/myshortcuts/NEISS_Cell_Phone_Project/NEISS_2002_2015.csv".
The minimum record length was 32767.
The maximum record length was 32767.
One or more lines were truncated.

SAS_Studio.jpg
Reeza
Super User

@_maldini_ wrote:

This seems to read in the entire dataset as 1 row...Hmm...

 

NOTE: 1 record was read from the infile "/folders/myshortcuts/NEISS_Cell_Phone_Project/NEISS_2002_2015.csv".
The minimum record length was 32767.
The maximum record length was 32767.
One or more lines were truncated.

That was the point - import whole line to help debug the issue and determine what SAS is 'seeing'. 

Kurt_Bremser
Super User

Use one of the better text tools (eg notepad++) to inspect the csv file. It allows you to see how lines are ended ("DOS/Windows" or "UNIX" in the status line), display special characters, and you can even view your file in HEX mode.

_maldini_
Barite | Level 11

@Kurt_Bremser Thanks for the assistance. I'm not sure what this means:

 

<"DOS/Windows" or "UNIX" in the status line>

 

Here is some sample data from the .csv file.

 

CPSC_Case_no,trmt_date,psu,weight,stratum,age,sex,race,race_other,diag,diag_other,body_part,disposition,location,fmv,prod1,prod2,narr1,narr2,new_var_1,new_var_2 151260474,42363,8,4.9655,C,12,1,1,,59,,76,1,4,0,550,,"12YOM RESTRAINED BACK PASSENGER, HIS CAR T-BONED ANOTHER CAR, HIT FACE",ON CELL PHONE; EYELID LAC.,cell phone, 160211064,42361,90,4.9655,C,10,1,3,HISPANIC,71,SUBCONJ. HEMORRHAGE,77,1,0,0,550,4057,"10 YOM WAS LAYING DOWN WHEN A ""BIG"" CELL PHONE FELL OFF A TABLE & HIT",HIM IN THE LT. EYE -HEMORRHAGE IN EYEBALL.DX; SUBCONJUNCTIVAL HEMORRHAG,cell phone, 151254433,42360,32,4.9655,C,209,1,0,,59,,88,1,0,0,550,,"9 MOM PLAYING WITH DADS CELL PHONE, HIT HIMSELF IN THE MOUTH WITH IT DX",LACERATION TO GUM,cell phone, 151251194,42359,50,74.8813,L,20,2,0,,53,,75,1,1,0,550,4056,"20 YOF HEAD CONTUSION, LOOKING DOWN AT HER PHONE WHEN SHE TURNED HER HE","AD HITTING COAT RACK, NO LOC",0, 151246366,42358,78,97.9239,M,18,1,2,,71,GSW,92,1,1,0,550,,"18 YOM INJURED FINGER,SHOT FINGER WHILE TALKING ON PHONE,2 DAYS AGO.","DX-FRACTURE MIDDLE FINGER,HEALING GSW",0, 151248259,42357,32,4.9655,C,8,1,2,,57,,76,1,4,0,550,,"8 YOM RESTRAINED BACKSEAT OF CAR, MOM DRIVING TALKING ON PHONE LOST CO","NTROL HIT TELEPHONE POLE, PT HIT FACE OF SIBS CARSEAT DX NASAL FRACTURE",0,likely cell 151244167,42356,67,16.565,V,20,1,0,,59,,76,1,1,0,550,,20YOM LAC TO FOREHEAD AFTER FRIEND THREW CELL PHONE AT HIM. BLDG ATTENU,ATED AT HOME: DX LAC FOREHEAD,cell phone, 160119975,42355,8,4.9655,C,15,1,1,,71,NASAL INJURY,76,1,2,0,1893,550,"15YOM WAS LOOKING DOWN WHILE TEXTING, THE BARN DOOR SWUNG OPEN HITTING","HIM IN FACE YESTERDAY, INCREASED PAIN TODAY; NASAL INJURY",texting, 151245016,42351,8,4.9655,C,204,2,1,,62,,75,1,0,0,550,,4MOF HIT ON HEAD WHEN 2YO SIBLING THREW A CELL PHONE WHILE HAVING A TRA,NTRUM; HEAD INJURY,cell phone, 151232122,42351,61,16.565,V,202,2,3,UNKNOWN,53,,76,1,1,0,550,,2 MOF HIT ON THE FOREHEAD BY A CELL PHONE THAT HAD BEEN THROWN BY HER,"BROTHER, DX: CONTUSION OF FOREHEAD",cell phone,

Reeza
Super User

@_maldini_ The code I provided didn't work? If not, can you post the log? Also, try with LF/CRLF instead of CR 🙂

 

Filename myfile 'path to csv.csv' TERMSTR=CR;

Proc import out =want datafile=myfile DBMS=CSV replace; *rest of options;
Run;
_maldini_
Barite | Level 11

@Reeza The log is attached. The error is the same using all 3 TERMSTR options. The data is also attached as a .txt file.

 

Note: I had a friend save the file as a .csv file in Excel on a Windows machine. PROC IMPORT w/o the FILENAME statement worked fine.

 

Log_Program_1.jpg

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 27 replies
  • 5754 views
  • 14 likes
  • 7 in conversation