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

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 ;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

7 REPLIES 7
ballardw
Super User

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.

adamsonb
Fluorite | Level 6

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.

adamsonb
Fluorite | Level 6
I added informat ProviderSpecialtyOther $200.; above the input statement but am still only getting "dentistry" rather than "dentistry, general"
LinusH
Tourmaline | Level 20

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

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;
Ksharp
Super User

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;
Astounding
PROC Star

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 7 replies
  • 1734 views
  • 1 like
  • 6 in conversation