BookmarkSubscribeRSS Feed
SASFanTodd
Calcite | Level 5

Hello All,

I'm very new to SAS (started training in E.G.5.1 today) and this may be putting the cart before the horse but I need to ask anyway.  I have the need to import .txt files which are healthcare claims 837p/x12 formated files.  Within each file, segments/records are broken up by a "~".  However, when I try to import the file delimiting the file by the "~" using EG, it tells me I cannot.  Here is just a little more detail using an example.  An 837/x12 file is basically 1 long string/record of data containing segments/records which can be delimeted by a "~" (in most cases) and looks something like this "ISA*00*          *00*        xxxxxxxxxx~GS*xxx    xxxxxx      xxxxxxxxxxxxx           xxx~ST*      xxxxxx~BHT*xxxxx     *xxxxxxxxxxxxxxxxxxxxxxxxxxxx  xxxxxxxxxxxxxxxxxxxxx~........and so on..............~IEA*xxxxxxxxxxx~" with each file beginning with an ISA segment/record and ending with an IEA segment/record.

As you can see, each segment/record is very different and contains different types of data with segments/records of different lengths which needs to be parsed out base on how the segment begins (i.e. ISA, GS, ST, etc).  I only know enough SQL to be dangerous but was able to load the files in SQL by having a table with 1 field/column/variable lets say 1000 characters long.  My load package had each column delimited by the ~ and worked just fine with 1 record per input segment/record.  Currently, we do not have SQL connected to SAS and my company would like to avoid that if possible however we may not be able to if SAS cannot load this directly.  As I stated earlier, we are brand new to SAS and this is my first attempt at this.  We knew this may be a sticky spot so wanted to try to solve this issue as soon as possible.

A little extra background - We are a small shop with just myself and another person as developers.  Like I said, I have just enough SQL knowledge to be dangerous and basically just used it to house tables which our previous BI tool (WebFOCUS) would use.  SQL just contained the stand alone tables and we used the BI tool to create datamarts and such for reporting against.  The company would like to avoid the SQL part if at all possible.

Thanks Much!

   

2 REPLIES 2
TomKari
Onyx | Level 15

Looks like you didn't get any bites here, either.

Send me a direct message, let me know your email address, and we'll discuss some other options offline.

Tom

Patrick
Opal | Level 21

I can't think of any data structure stored as text file which couldn't be converted into a SAS table. But if the structure is a bit special like yours then it might need some manual SAS coding to convert the text into a SAS table.

It would be great if you could post a representative sample of your data as file attachment and then also describe how the result table should look like (what rows you're expecting).

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
  • 2 replies
  • 1772 views
  • 0 likes
  • 3 in conversation