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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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

I think that did the trick, thank you!

PGStats
Opal | Level 21

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
Patrick
Opal | Level 21

@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
Fluorite | Level 6
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

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
  • 5 replies
  • 1089 views
  • 4 likes
  • 4 in conversation