07-30-2012 07:21 AM
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.
07-31-2012 07:02 PM
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).