09-10-2017 10:17 PM
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.
09-10-2017 11:48 PM
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.
09-11-2017 07:06 AM
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).