BookmarkSubscribeRSS Feed
ilikesas
Barite | Level 11

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

4 REPLIES 4
Reeza
Super User

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

ilikesas
Barite | Level 11

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
Super User

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
Barite | Level 11

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

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!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 4 replies
  • 873 views
  • 2 likes
  • 2 in conversation