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
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.
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.
I think that did the trick, thank you!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.