Reading NCPDP file

jasonDMNK — Mon, 30 Apr 2007 18:54:34 GMT

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;

Re: Reading NCPDP file

deleted_user — Thu, 03 May 2007 10:29:54 GMT

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

topic Re: Reading NCPDP file in SAS Health and Life Sciences

Reading NCPDP file

Re: Reading NCPDP file