Hello Everyone, Having a few issues with moving data I collected in Excel into SAS. The first problem is that the data in Excel is on a massive scale (about 66,000 observations across 10 variables where each variable captures a non-chronologically sorted time series. The way I collected the data, I set up each variable into a folder of spreadsheets [so 10 folders] where each folder contains 66 workbooks [1,000 observations per each workbook]). This isn't too big of a problem because once I find a good way to get a single workbook into SAS I can easily just create a for loop and automate the rest. The second (and biggest) problem is the way the data is structured. As a reference, the data looks like the following: ID Amount 140011 451 140012 167 153 34 N/A 43 193 14002C N/A 140031 142 45 And the format I need it to be in is the following: ID Amount 140011 451 140012 167 140012 153 140012 34 140012 N/A 140012 43 140012 193 14002C N/A 140031 142 140031 45 A few things to note: 1 - In the first table, a blank means the time series ends. 2 - In the first table, N/A means that the data point for that specific point in time is missing, however, I need to keep it intact so that when I combine it with the other 9 variables I can get the time series' lined up correctly (and the variable may not be missing for the other 9 variables). There is an exception to this though. If the first column is N/A (for example, 14002C) then it might mean that there is no information at all (across any of the variables), or it could also mean that only the information for this specific variable is missing. In other words, if an ID just has no data in the database then the Amount column will show N/A in only that column. However, if an ID has only one piece of data but that data is missing for this variable, then the Amount column will also show N/A in only that column. 3 - The scale is massive - some of the time series have upwards of 12,000 data points (i.e., data in 12,000 Excel columns), while others have none. I've tried importing directly from Excel but had little luck as SAS seems to limit Excel imports to the first 255 columns, which is an obvious issue. 4 - There are only two column headings in the original data - one for the ID and another for the first column of the non-sorted time series data. Any more data in the time series shows up in un-labeled, subsequent columns. 5 - Some of the 10 variables are numeric (amounts), some are characters (names), and same are dates - so any code needs to be easily changeable or robust enough to accommodate different variable types. 6 - The relative order of the data needs to be retained when it moves from one row to one column as it needs to line up correctly with 9 other variables (only one of which is the date) Any code-heads with thoughts or solutions? I've attached an example of the data (it is in .csv format, though, not the native .xlsx as the site wouldn't let me upload a .xlsx file) in the event it might help.
... View more