DATA Step, Macro, Functions and more

Escaping delimiters without dsd

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Escaping delimiters without dsd

I'm trying to inport some data of the following structure:

!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!

 

However the comma in the string is being read as a delemiter.

 

Here is my code so far:

 

data test;

infile "P:\crv_1208_prov_20151228.txt"

dlm=",!"

truncover;

input

ProviderID :4.0

ProviderSpecialty :2.0

ProviderSpecialtyOther :$200.

ProviderType :2.0

ProviderTypeOther :$200.

PracticeType :$5.

PracticeTypeOther :$200.

ProviderYears

itemNumber :$9.

extractDate :yymmdd8.

providingSite

ddVersion ;

 


Accepted Solutions
Solution
‎12-29-2015 08:10 AM
Super User
Posts: 10,046

Re: Escaping delimiters without dsd

Change ! into " 

 

 

data have;
infile '/folders/myfolders/temp.txt' dsd truncover lrecl=2000;
input @;
_infile_=translate(_infile_,'"','!'); 
input
ProviderID :4.0
ProviderSpecialty :2.0
ProviderSpecialtyOther :$200.
ProviderType :2.0
ProviderTypeOther :$200.
PracticeType :$5.
PracticeTypeOther :$200.
ProviderYears
itemNumber :$9.
extractDate :yymmdd8.
providingSite
ddVersion ;
run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Escaping delimiters without dsd

Try using INFORMAT or ATTRIB statements to define the informat for the variables. before the Input statement. When the format is specified in the input then there is sort of "forced read" for the number of columns.

New Contributor
Posts: 3

Re: Escaping delimiters without dsd

Won't that result in sas reading for a specified number of columns? Because that is a no go as the length varries from one line to the next.

New Contributor
Posts: 3

Re: Escaping delimiters without dsd

I added informat ProviderSpecialtyOther $200.; above the input statement but am still only getting "dentistry" rather than "dentistry, general"
Super User
Posts: 5,441

Re: Escaping delimiters without dsd

The reason for this is that a delimiter is just one char, specifying more than one tells SAS that there are alternate delimiters.

If you files are not large, I suggest that you pre-process them, changing all ! to ". Then you should be able to import the data easily with DLM=',' DSD;

Data never sleeps
Respected Advisor
Posts: 4,173

Re: Escaping delimiters without dsd

Try DLMSTR= as shown below.

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;
Solution
‎12-29-2015 08:10 AM
Super User
Posts: 10,046

Re: Escaping delimiters without dsd

Change ! into " 

 

 

data have;
infile '/folders/myfolders/temp.txt' dsd truncover lrecl=2000;
input @;
_infile_=translate(_infile_,'"','!'); 
input
ProviderID :4.0
ProviderSpecialty :2.0
ProviderSpecialtyOther :$200.
ProviderType :2.0
ProviderTypeOther :$200.
PracticeType :$5.
PracticeTypeOther :$200.
ProviderYears
itemNumber :$9.
extractDate :yymmdd8.
providingSite
ddVersion ;
run;
Super User
Posts: 5,518

Re: Escaping delimiters without dsd

Here's a method that just reads the extra commas as a variable to be dropped later.

 

data want;

infile "P:\crv_1208_prov_20151228.txt" dlm="!" truncover;

length ProviderSpecialtyOther ProviderTypeOther PracticeTypeOther $ 200

       PracticeType $ 5 itemNumber $ 9 dummy $ 1;

informat extractDate yymmdd8.;

input ProviderID dummy

      ProviderSpecialty dummy

      ProviderSpecialtyOther dummy

      ProviderType dummy

      ProviderTypeOther dummy

      PracticeType dummy

      PracticeTypeOther dummy

      ProviderYears dummy

      itemNumber dummy

      extractDate dummy

      providingSite dummy

      ddVersion

      ;

drop dummy;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 339 views
  • 1 like
  • 6 in conversation