DATA Step, Macro, Functions and more

Using PROC IMPORT to generate DATA step syntax

Accepted Solution Solved
Reply
Regular Contributor
Posts: 199
Accepted Solution

Using PROC IMPORT to generate DATA step syntax

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.

 

 


Accepted Solutions
Solution
‎09-21-2016 01:18 PM
Super User
Posts: 19,869

Re: Using PROC IMPORT to generate DATA step syntax

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

 

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


All Replies
Super User
Posts: 3,260

Re: Using PROC IMPORT to generate DATA step syntax

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

Regular Contributor
Posts: 199

Re: Using PROC IMPORT to generate DATA step syntax

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.

 

 

 

 

Super User
Posts: 10,044

Re: Using PROC IMPORT to generate DATA step syntax

Try Adding guessingrows=32767 option .
Regular Contributor
Posts: 199

Re: Using PROC IMPORT to generate DATA step syntax

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

Super User
Posts: 19,869

Re: Using PROC IMPORT to generate DATA step syntax

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;

 

Super User
Posts: 19,869

Re: Using PROC IMPORT to generate DATA step syntax

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

Regular Contributor
Posts: 199

Re: Using PROC IMPORT to generate DATA step syntax

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_.
Super User
Super User
Posts: 7,076

Re: Using PROC IMPORT to generate DATA step syntax

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;
Regular Contributor
Posts: 199

Re: Using PROC IMPORT to generate DATA step syntax

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
Super User
Posts: 19,869

Re: Using PROC IMPORT to generate DATA step syntax


jcorroon 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'. 

Super User
Posts: 7,854

Re: Using PROC IMPORT to generate DATA step syntax

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 199

Re: Using PROC IMPORT to generate DATA step syntax

Posted in reply to KurtBremser

@KurtBremser 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,

Solution
‎09-21-2016 01:18 PM
Super User
Posts: 19,869

Re: Using PROC IMPORT to generate DATA step syntax

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

 

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

Proc import out =want datafile=myfile DBMS=CSV replace; *rest of options;
Run;
Regular Contributor
Posts: 199

Re: Using PROC IMPORT to generate DATA step syntax

[ Edited ]

@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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 28 replies
  • 719 views
  • 14 likes
  • 7 in conversation