topic Re: Using PROC IMPORT to generate DATA step syntax in SAS Programming

Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 01:30:19 GMT

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.

Re: Using PROC IMPORT to generate DATA step syntax

SASKiwi — Tue, 20 Sep 2016 02:13:30 GMT

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

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 03:07:01 GMT

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.

Re: Using PROC IMPORT to generate DATA step syntax

Ksharp — Tue, 20 Sep 2016 03:59:05 GMT

Try Adding guessingrows=32767 option .

Re: Using PROC IMPORT to generate DATA step syntax

Ksharp — Tue, 20 Sep 2016 04:03:44 GMT

Try adding datarow=5 option .

Re: Using PROC IMPORT to generate DATA step syntax

Reeza — Tue, 20 Sep 2016 04:31:08 GMT

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

Re: Using PROC IMPORT to generate DATA step syntax

Tom — Tue, 20 Sep 2016 04:43:11 GMT

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;

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 04:52:12 GMT

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

_______

ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,

_DEBUG_.

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 04:54:06 GMT

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

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 04:54:33 GMT

Same error.

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 04:58:08 GMT

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.

Re: Using PROC IMPORT to generate DATA step syntax

Reeza — Tue, 20 Sep 2016 06:24:02 GMT

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;

Re: Using PROC IMPORT to generate DATA step syntax

Reeza — Tue, 20 Sep 2016 06:25:05 GMT

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

Re: Using PROC IMPORT to generate DATA step syntax

Kurt_Bremser — Tue, 20 Sep 2016 07:09:47 GMT

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.

Re: Using PROC IMPORT to generate DATA step syntax

Ksharp — Tue, 20 Sep 2016 09:39:57 GMT

That is really weird . Maybe your file is stream file(don't have line break character).
Can you post some sample file here? we can check what is going on .

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 15:58:26 GMT

Thanks!

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,

Re: Using PROC IMPORT to generate DATA step syntax

_maldini_ — Tue, 20 Sep 2016 16:00:13 GMT

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

Re: Using PROC IMPORT to generate DATA step syntax

ballardw — Tue, 20 Sep 2016 16:27:13 GMT

It may be better to post an attachment made from the source file as txt. Pasting things into the forum changes the content by inserting html tags and such an is likely to lose certain types of characters.

Re: Using PROC IMPORT to generate DATA step syntax

Tom — Tue, 20 Sep 2016 16:36:36 GMT

No end of line characters appear in the posting, but this website might have removed them in the posting.

Did you try using the LIST statement to see what is in your data?

Here is a simple program to demonstrate what you should see in normal text files with either CR+LF, LF or CR as the end of line.

filename test1 temp;
filename test2 temp;
filename test3 temp;
data _null_;
  file test1 recfm=n ;
  put 'Uses CR+LF' '0D0A'x 'Between lines.' '0D0A'x ;
  file test2 recfm=n ;
  put 'Uses LF' '0A'x 'Between lines.' '0A'x ;
  file test3 recfm=n ;
  put 'Uses CR' '0D'x 'Between lines.' '0D'x ;    
run;
data _null_;
  infile test1 recfm=f ;
  input; list;
  infile test2 recfm=f ;
  input; list;
  infile test3 recfm=f ;
  input; list;
run;

Here is the result of this test program.

 RULE:     ----+----1----+----2----+----3----+----4----+----5
 
 1   CHAR  Uses CR+LF..Between lines... 28
     ZONE  5767245244004677666266667200
     NUMR  5353032BC6DA254755E0C9E53EDA
 
 1   CHAR  Uses LF.Between lines.. 23
     ZONE  57672440467766626666720
     NUMR  53530C6A254755E0C9E53EA
 
 1   CHAR  Uses CR.Between lines.. 23
     ZONE  57672450467766626666720
     NUMR  5353032D254755E0C9E53ED

For your file what do you see as the ZONE and NUMR values for the character bwteen "new_var_2" and "151260474" ?

Re: Using PROC IMPORT to generate DATA step syntax

Reeza — Tue, 20 Sep 2016 19:12:27 GMT

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