BookmarkSubscribeRSS Feed
jasonDMNK
Calcite | Level 5
I'm not sure if anyone here has tried importing the NCPDP encounter file (?) but I'm having some problems. The file I'm working with comes from a tape system so records wrap from one line to the next in most cases. There are character strings to indicate the start of specific variables, and I have tried using @ '' in my input statement to read these variables. For some reason it works fine on the first variable and then ignores all other variables for the record.

I tried using the length = linelen trick to make sure that there wasn't an additional delimiter that was sending SAS to the next row, and the line length comes back as the correct length. Any ideas? The code I'm running is below.

data detail ;
infile "e:\sasdata\test.mco.txt" dlm='02'x truncover length=linelen;
input
@2 Segment $2. @;
if Segment = 'G1' then
input
TransactionReferenceNo $10.
BinNumber $6.
Version $2.
TransactionCode $2.
ProcessorControlNo $10.
TransactionCount 1.
ProviderIDQualifier $2.
ProviderID $15.
ServiceDate 8.
@'C2' CardholderID $ @;
input @'CC' CardholderFirstName $ @;
input @'CD' CardholderLastName $ @;
input PlanID $8.
RxSegment $2.
@'EM' RxReferenceNoQualifier $1. @;
input @'D2' RxReferenceNo $7. @;
input @'E1' ServiceIDQualifier $2. @;
input @'D7' ServiceID $19. @;
input @'E7' Quantity 10. @;
input @'D3' FillNumber 2. @;
input @'D5' DaysSupply 3. @;
input @'D8' DAWCode $1. @;
input @'DQ' UCCharge 9. @;
input @'EZ' PrescriberIDQualifier $2. @;
input @'DB' PrescriberID $15.
;

varlen=linelen;

run;
1 REPLY 1
deleted_user
Not applicable
of course, this is a data loading problem rather than Health Care, but I think I can help.

May I assume you have the data in the order of your input statements, and always separated by the delimiter you defined on your infile statement?

Change your policy, from reading at specific positions with specific lengths, to the style which supports delimited data fields with varying positions.

Infile statements support delimited separated data (DSD) with the infile option DSD.
The input statement supports DSD by allowing input at the next "available" position (using no definitions like @NNN or @'string').
Also input statements support DSD best, with no informat lengths defined on the input statement (because the implication of DSD is that we do not know the lengths and the SAS software will use the lengths we define on an input statement, to parse the line)

What you need to do then, is predefine your variable lengths along with any non-default informats (especially for dates), before the input statement. Then just list your variables on the input statement, in input order. A bit like:
data details ;
length TransactionReferenceNo $10 BinNumber $6 Version $2 TransactionCode $2 ProcessorControlNo $10 TransactionCount 3 ProviderIDQualifier $2 ProviderID $15 ServiceDate 6 CardholderID $8 CardholderFirstName $8 CardholderLastName $20 PlanID $8 RxSegment $2 RxReferenceNoQualifier $1 RxReferenceNo $7 ServiceIDQualifier $2 ServiceID $19 Quantity 8 FillNumber 3 DaysSupply 3 DAWCode $1 UCCharge 8 PrescriberIDQualifier $2 PrescriberID $15 ;

attrib ServiceDate informat= mmddyy. format= date9. ;

infile "e:\sasdata\test.mco.txt" dlm= '02'x lrecl= 10000 ; * support lines wider than 256 ;

/* If you are really confident, you could use the short-form
input TransactionReferenceNo -- PrescriberID ;
otherwise, list all variables ................*/

input TransactionReferenceNo BinNumber Version TransactionCode ProcessorControlNo TransactionCount ProviderIDQualifier ProviderID ServiceDate CardholderID CardholderFirstName CardholderLastName PlanID RxSegment RxReferenceNoQualifier RxReferenceNo ServiceIDQualifier ServiceID Quantity FillNumber DaysSupply DAWCode UCCharge PrescriberIDQualifier PrescriberID ;

run;

Even if your data are spreading over more than one line, the approach above works, provided that the lines break between variables.

Of course, I cannot see a sample of your data. If my assumptions are wrong, better help may be available, if you post a sample of your data (but please replace the '02'x delimiter with a "printable" character)

Hope my description above is clear

Good Luck

Peter Crawford

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!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 1 reply
  • 1182 views
  • 0 likes
  • 2 in conversation