Help with crazy delimiters in TXT file

[Jsanders]

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

[Astounding · Accepted solution]

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.

[Jsanders]

I think that did the trick, thank you!

[PGStats]

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;

[Patrick]

@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;

[Jsanders]

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