Using PROC IMPORT to generate DATA step syntax

[_maldini_]

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.

[SASKiwi]

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

[_maldini_]

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]

Try Adding guessingrows=32767 option .

[_maldini_]

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]

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]

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_]

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]

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_]

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.

[Reeza]

---

@_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]

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_]

@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 · Accepted solution]

@_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_]

@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.