data structure capabilities in proc iml

[ilikesas]

Hi,

to my knowledge, in order import data and work on it in SAS the data has to be in table form structure, i.e, varaible headers and observations in columns.

Recently I was working with data that had a very "un-table" like structure, and importng it into SAS was a mess. In the end I was able to transform its data into table form using Excel VBA, by working with cells and finding their structure pattern. But Excel/VBA is limited and sometimes I witnessed it go crazy when asked to think too much.

Nevertheless the code to transform the data structure was fairly easy because of the grid-nature of Excel, and a grid is somewhat like a matrix, so I was wandering if proc iml has data structure transforming capabilities like VBA, if not better.

Thank you

[Reeza]

SAS Base would likely be used to do the clean up and then import the data into IML. 

[ilikesas]

Here is a small sample of the data with the before and after structures (I sent it to many people already so it is safe)

[Reeza]

It looks fairly well structured to me, I've actually had to deal with a file exactly in that format before - measurements made daily by nurses into an Excel file.

Anyways, save as CSV and import with a data step, they seem to come in pairs of 3 lines at a time and then goes across for a varying amount of days. You can either output each entry to a separate line and recombine it afterwards or you could create a really long variable string and read it in. 

You can use the double @@ to hold the line.

Or you could read the whole line in at once, all three and then parse....

There's a lot of options here, I'm not sure why you felt Excel was the best choice. It obviously will work - and I've done it that way before, but I definitely like the reproduceability of code where the instructions are a lot less and XLSM workbooks aren't required.

[ilikesas]

Could you please provide some lines of codes as an example? Just so that I get a general sense of the code and I will explore and develop it myself (or a link to an example).

Thanks