turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Health Care and Pharma
- /
- SAS in Health Care Related Fields
- /
- Reading NCPDP file

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2007 02:54 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jasonDMNK

05-03-2007 06:29 AM

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

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