Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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.

 


Accepted Solutions
Solution
‎03-17-2016 12:01 AM
Super User
Posts: 9,682

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

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;

View solution in original post


All Replies
Super User
Posts: 9,682

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

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;
Occasional Contributor
Posts: 8

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

[ Edited ]

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

Solution
‎03-17-2016 12:01 AM
Super User
Posts: 9,682

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

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;
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 364 views
  • 2 likes
  • 2 in conversation