BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
willy06251
Fluorite | Level 6

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

3 REPLIES 3
Ksharp
Super User

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;
willy06251
Fluorite | Level 6

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

Ksharp
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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