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