DATA Step, Macro, Functions and more

Help with crazy delimiters in TXT file

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Help with crazy delimiters in TXT file

Hi there,

I have the following text file I need to input using an infile statement. 

 

!ProviderID!,!ProviderSpecialty!,!ProviderSpecialtyOther!,!ProviderType!,!ProviderTypeOther!,!PracticeType!,!PracticeTypeOther!,!ProviderYears!,!itemNumber!,!extractDate!,!providingSite!,!ddVersion!
!4!,!28!,!-99999!,!6!,!-99999!,!5!,!-99999!,!19!,!1/1803!,!20140930!,!72!,!3.2!
!5!,!28!,!-99999!,!6!,!-99999!,!6|7!,!-99999!,!18!,!2/1803!,!20140930!,!72!,!3.2!

 

The data I need is within the exclamation points and the delimiter is a comma. Is there a way to strip the exclamation points and have dlm=comma? I tried using the 'dlmstr' option and was not successful.

 

Best,

Joanne


Accepted Solutions
Solution
‎12-29-2015 08:51 AM
Super User
Posts: 5,518

Re: Help with crazy delimiters in TXT file

Easiest might be to treat both commas and exclamation points as delimiters:

 

infile whatever dlm=',!';

 

If that doesn't do the job, we can explore other possibilities.

 

Do NOT add the DSD option in that case ... and it will complicate things if you have more than 2 consecutive delimiters.

View solution in original post


All Replies
Solution
‎12-29-2015 08:51 AM
Super User
Posts: 5,518

Re: Help with crazy delimiters in TXT file

Easiest might be to treat both commas and exclamation points as delimiters:

 

infile whatever dlm=',!';

 

If that doesn't do the job, we can explore other possibilities.

 

Do NOT add the DSD option in that case ... and it will complicate things if you have more than 2 consecutive delimiters.

New Contributor
Posts: 3

Re: Help with crazy delimiters in TXT file

Posted in reply to Astounding

I think that did the trick, thank you!

Respected Advisor
Posts: 4,936

Re: Help with crazy delimiters in TXT file

An alternative that would provide proper handling of empty fields would be 

 

data have;
infile "yourfile.txt" dsd firstobs=2;
input @;
_infile_ = translate (_infile_,"""", "!");
input ProviderID ProviderSpecialty ProviderSpecialtyOther ProviderType $
	ProviderTypeOther PracticeType $ PracticeTypeOther ProviderYears itemNumber $
	extractDate :yymmdd8. providingSite ddVersion;
run;
PG
Respected Advisor
Posts: 4,173

Re: Help with crazy delimiters in TXT file

@Jsanders

Is this a study question? @adamsonb asked exactly the same here: https://communities.sas.com/t5/Base-SAS-Programming/Escaping-delimiters-without-dsd/m-p/241076#U2410...

 

I believe "DLMSTR=" could be used to solve the problem.

data test;
/*  infile "P:\crv_1208_prov_20151228.txt"*/
  infile datalines
    dlmstr="!,!" dsd
    truncover;
  input @2
    ProviderID :4.0
    ProviderSpecialty :2.0
    ProviderSpecialtyOther :$200.
    ProviderType :2.0
    ProviderTypeOther :$200.
    PracticeType :$5.
    PracticeTypeOther :$200.
    ProviderYears
    itemNumber :$9.
    extractDate :yymmdd8.
    providingSite
    ddVersion;
  datalines;
!434!,!23!,!-99999!,!6!,!-99999!,!6|7!,!-99999!,!10!,!311/1803!,!20140930!,!72!,!3.2!
!438!,!97!,!DENTISTRY, GENERAL!,!97!,!TSBDE PROVIDER!,!6|7!,!-99999!,!-99999!,!312/1803!,!20140930!,!72!,!3.2!
!438!,!97!,!!,!97!,!!,!!,!!,!-99999!,!312/1803!,!20140930!,!72!,!3.2!
;
run;
New Contributor
Posts: 3

Re: Help with crazy delimiters in TXT file

Yes! Thank you Patrick. I started working on this issue then had to pass it off to a colleague. I asked him to post to the SAS community since you all are so helpful.

Best,
Joanne
🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 316 views
  • 4 likes
  • 4 in conversation