topic Re: Escaping delimiters without dsd in SAS Programming

Escaping delimiters without dsd

adamsonb — Mon, 28 Dec 2015 20:29:33 GMT

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 ;

Re: Escaping delimiters without dsd

ballardw — Mon, 28 Dec 2015 21:36:10 GMT

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.

Re: Escaping delimiters without dsd

adamsonb — Mon, 28 Dec 2015 21:40:44 GMT

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.

Re: Escaping delimiters without dsd

adamsonb — Mon, 28 Dec 2015 21:58:34 GMT

I added informat ProviderSpecialtyOther $200.; above the input statement but am still only getting "dentistry" rather than "dentistry, general"

Re: Escaping delimiters without dsd

LinusH — Mon, 28 Dec 2015 22:42:50 GMT

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;

Re: Escaping delimiters without dsd

Ksharp — Tue, 29 Dec 2015 01:14:41 GMT

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;

Re: Escaping delimiters without dsd

Astounding — Tue, 29 Dec 2015 03:03:20 GMT

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;

Re: Escaping delimiters without dsd

Patrick — Tue, 29 Dec 2015 05:25:45 GMT

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;