topic Re: data step import - what is the right delimeter to use? in SAS Procedures

data step import - what is the right delimeter to use?

willy06251 — Thu, 17 Mar 2016 00:03:14 GMT

Hi guys.

I am trying to import a text file by using a datastep import. The following is the code I am using

DATA f_&filename;
    FORMAT
    location_id      $CHAR10.
    pasid            $CHAR20.
    address_type 	 $CHAR10.
    address			 $CHAR150.
    locality         $CHAR100.
    postcode         $CHAR25.
    state            $CHAR25.
    country          $CHAR25.
    from_date 		 $CHAR10.
    to_date			 $CHAR10.
	;
 INFILE "&fullLink"
	LRECL=275 firstobs=2
    DLM=","
    MISSOVER
    DSD ;
  INPUT
    location_id      : $CHAR10.
    pasid            : $CHAR20.
    address_type 	 : $CHAR10.
    address			 : $CHAR150.
    locality         : $CHAR100.
    postcode         : $CHAR25.
    state            : $CHAR25.
    country          : $CHAR25.
    from_date 		 : $CHAR10.
    to_date			 : $CHAR10.
;
 RUN;

The most of the records are brought in properly without any column mis-alignment (Like row 1 in the attached test.txt file).

However there are a about a dozen of records that are in the form row 2 is in - What is happening is

""LIONS BRAE", EVERARD RD.," which should be taken into the address column is being broken into

""LIONS BRAE", and EVERARD RD.,

Should I have set my delimeter to be something other than "," to prevent this from happening?

SAS experts please advise me.

Many thanks.

Re: data step import - what is the right delimeter to use?

Ksharp — Thu, 17 Mar 2016 01:22:48 GMT

How about this :

filename x '/folders/myfolders/correct.txt';
data _null_;
 infile '/folders/myfolders/test.txt';
 file x;
 input;
 _infile_=prxchange('s/"([^",]+)"/$1/',-1,_infile_);
 put _infile_;
run;
proc import datafile=x out=have dbms=csv replace;
run;

Re: data step import - what is the right delimeter to use?

willy06251 — Thu, 17 Mar 2016 02:36:47 GMT

Hi Xia.

I must stick with the data step import I have written as this is one of the 100 files that needs to be put in the same format.

Would you be able to suggest a solution that can from modifying the current data step?

Many thanks

Re: data step import - what is the right delimeter to use?

Ksharp — Thu, 17 Mar 2016 02:52:18 GMT

Of course.


data have;
 infile '/folders/myfolders/test.txt' firstobs=2 dsd truncover;
 input @;
 _infile_=prxchange('s/"([^",]+)"/$1/',-1,_infile_);
 INPUT
    location_id      : $CHAR10.
    pasid            : $CHAR20.
    address_type 	 : $CHAR10.
    address			 : $CHAR150.
    locality         : $CHAR100.
    postcode         : $CHAR25.
    state            : $CHAR25.
    country          : $CHAR25.
    from_date 		 : $CHAR10.
    to_date			 : $CHAR10.
;
run;