Using PROC IMPORT to generate DATA step syntax

[ballardw]

Nothing funny about the text file. Imported fine as basic CSV though I did remove the space before MAC in the file name and the path is different on my machine..

 /**********************************************************************
 *   PRODUCT:   SAS
 *   VERSION:   9.2
 *   CREATOR:   External File Interface
 *   DATE:      20SEP16
 *   DESC:      Generated SAS Datastep Code
 *   TEMPLATE SOURCE:  (None Specified.)
 ***********************************************************************/
    data WORK.junk                                    ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'D:\NEISS_2002_2015_20MAC.txt' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat CPSC_Case_no best32. ;
       informat trmt_date best32. ;
       informat psu best32. ;
       informat weight best32. ;
       informat stratum $1. ;
       informat age best32. ;
       informat sex best32. ;
       informat race best32. ;
       informat race_other $16. ;
       informat diag best32. ;
       informat diag_other $26. ;
       informat body_part best32. ;
       informat disposition best32. ;
       informat location best32. ;
       informat fmv best32. ;
       informat prod1 best32. ;
       informat prod2 best32. ;
       informat narr1 $87. ;
       informat narr2 $96. ;
       informat new_var_1 $10. ;
       informat new_var_2 $14. ;
       format CPSC_Case_no best12. ;
       format trmt_date best12. ;
       format psu best12. ;
       format weight best12. ;
       format stratum $1. ;
       format age best12. ;
       format sex best12. ;
       format race best12. ;
       format race_other $16. ;
       format diag best12. ;
       format diag_other $26. ;
       format body_part best12. ;
       format disposition best12. ;
       format location best12. ;
       format fmv best12. ;
       format prod1 best12. ;
       format prod2 best12. ;
       format narr1 $87. ;
       format narr2 $96. ;
       format new_var_1 $10. ;
       format new_var_2 $14. ;
    input
                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 $
    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
    run;

[_maldini_]

Hmm..I'm using SAS University Edition. It works when the .csv file is saved on a Windows machine, but not my Mac....

[Reeza]

Tried it on my Mac with SAS UE. No issues using code with CR specified as TERMSTR. 

Post the exact code you submitted + your log if you're still having issues. Most likely you're missing something in your code. 

[Kurt_Bremser]

---

@_maldini_ wrote:

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

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

---

Well, good text tools recognize the end-of-line characters in the file. If it's only single LFs (0Ax), this is typical for UNIX. If it is CRLF (0D0Ax), this is the file format used in CP/M, DOS and subsequently Windows.

The editor then puts the "DOS/Windows" or "UNIX" into its status line to notify the user of the file format.

A good way to circumvent conversion problems is to use text or ASCI mode when copying text(!) files with FTP/SFTP. That automatically takes care of different codepages, end-of-line sequences and so on.

[Ksharp]

Try adding datarow=5 option .

[_maldini_]

Same error. 

[Ksharp]

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 .

[_maldini_]

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,

[ballardw]

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.

[Reeza]

TERMSTR is definitely CR.

The code I provided worked on my system, but it's not SAS UE which maybe the issue.

The exact code was as follows - I noticed that your proc import still had the file reference, not sure if you didn't replace it or if SAS log unhelpfully did. It doesn't in mine though.

filename outfile "C:\Downloads\NEISS_2002_2015 MAC.txt" termstr=CR;

proc import out=want datafile=outfile dbms=csv replace;
getnames=yes;
run;

Data step code generated was:

      data WORK.WANT    ;
      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
      infile OUTFILE delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
         informat CPSC_Case_no best32. ;
         informat trmt_date best32. ;
         informat psu best32. ;
         informat weight best32. ;
         informat stratum $1. ;
         informat age best32. ;
         informat sex best32. ;
         informat race best32. ;
         informat race_other $8. ;
         informat diag best32. ;
         informat diag_other $19. ;
         informat body_part best32. ;
         informat disposition best32. ;
         informat location best32. ;
         informat fmv best32. ;
         informat prod1 best32. ;
         informat prod2 best32. ;
         informat narr1 $73. ;
         informat narr2 $73. ;
         informat new_var_1 $10. ;
         informat new_var_2 $11. ;
         format CPSC_Case_no best12. ;
         format trmt_date best12. ;
         format psu best12. ;
         format weight best12. ;
         format stratum $1. ;
         format age best12. ;
         format sex best12. ;
         format race best12. ;
         format race_other $8. ;
         format diag best12. ;
         format diag_other $19. ;
         format body_part best12. ;
         format disposition best12. ;
         format location best12. ;
         format fmv best12. ;
         format prod1 best12. ;
         format prod2 best12. ;
         format narr1 $73. ;
         format narr2 $73. ;
         format new_var_1 $10. ;
         format new_var_2 $11. ;
      input
                  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 $
      ;
      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
      run;

[Reeza]

If I didn't specify TERMSTR it did not import. I get the same error.

Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.17 seconds
      cpu time            0.07 seconds

[Ksharp]

I can confirm Reeza's code is right.




 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 51         
 52         filename x '/folders/myfolders/NEISS_2002_2015 MAC.txt' termstr=cr;
 53         proc import datafile=x out=have dbms=csv replace;
 53       !                                                  run;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 54          /**********************************************************************
 55          *   PRODUCT:   SAS
 56          *   VERSION:   9.4
 57          *   CREATOR:   External File Interface
 58          *   DATE:      21SEP16
 59          *   DESC:      Generated SAS Datastep Code
 60          *   TEMPLATE SOURCE:  (None Specified.)
 61          ***********************************************************************/
 62             data WORK.HAVE    ;
 63             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 64             infile X delimiter = ',' MISSOVER DSD  firstobs=2 ;
 65                informat CPSC_Case_no best32. ;
 66                informat trmt_date best32. ;
 67                informat psu best32. ;
 68                informat weight best32. ;
 69                informat stratum $1. ;
 70                informat age best32. ;
 71                informat sex best32. ;
 72                informat race best32. ;
 73                informat race_other $8. ;
 74                informat diag best32. ;
 75                informat diag_other $19. ;
 76                informat body_part best32. ;
 77                informat disposition best32. ;
 78                informat location best32. ;
 79                informat fmv best32. ;
 80                informat prod1 best32. ;
 81                informat prod2 best32. ;
 82                informat narr1 $73. ;
 83                informat narr2 $73. ;
 84                informat new_var_1 $10. ;
 85                informat new_var_2 $11. ;
 86                format CPSC_Case_no best12. ;
 87                format trmt_date best12. ;
 88                format psu best12. ;
 89                format weight best12. ;
 90                format stratum $1. ;
 91                format age best12. ;
 92                format sex best12. ;
 93                format race best12. ;
 94                format race_other $8. ;
 95                format diag best12. ;
 96                format diag_other $19. ;
 97                format body_part best12. ;
 98                format disposition best12. ;
 99                format location best12. ;
 100               format fmv best12. ;
 101               format prod1 best12. ;
 102               format prod2 best12. ;
 103               format narr1 $73. ;
 104               format narr2 $73. ;
 105               format new_var_1 $10. ;
 106               format new_var_2 $11. ;
 107            input
 108                        CPSC_Case_no
 109                        trmt_date
 110                        psu
 111                        weight
 112                        stratum $
 113                        age
 114                        sex
 115                        race
 116                        race_other $
 117                        diag
 118                        diag_other $
 119                        body_part
 120                        disposition
 121                        location
 122                        fmv
 123                        prod1
 124                        prod2
 125                        narr1 $
 126                        narr2 $
 127                        new_var_1 $
 128                        new_var_2 $
 129            ;
 130            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 131            run;
 
 NOTE: The infile X is:
       Filename=/folders/myfolders/NEISS_2002_2015 MAC.txt,
       Owner Name=root,Group Name=vboxsf,
       Access Permission=-rwxrwx---,
       Last Modified=21Sep2016:13:35:43,
       File Size (bytes)=334927
 
 NOTE: 2161 records were read from the infile X.
       The minimum record length was 79.
       The maximum record length was 247.
 NOTE: The data set WORK.HAVE has 2161 observations and 21 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.03 seconds
       cpu time            0.02 seconds
       
 
 2161 rows created in WORK.HAVE from X.
   
   
   
 NOTE: WORK.HAVE data set was successfully created.
 NOTE: The data set WORK.HAVE has 2161 observations and 21 variables.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           1.66 seconds
       cpu time            0.24 seconds
       
 
 132        
 133        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 143        

[Tom]

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