I've trawled help files and internet articles to no avail on this subject and wondered if anyone has successfully carried out what I'm trying to do.
I have a field in a data set that contains information about the variable names, size and values in a long string. I'm trying to parse this data out into actual variables and values within the same observation.
The variable (event_data) I'm trying to parse contains the value; 26CRDT2010-05-13-07.30.00.000000
In the above, the first 2 characters always denote the length which in this case is 26. The characters that always start at the 3rd for 4 characters is the field name and from the 7th character to the length denoted from the first 2 (26 in this case) is always the value of the field.
So I'm trying to create the field of CRDT with the value of 2010-05-13-07.30.00.000000 in this observation.
I feel that SAS should be able to do this but I've not managed to do it successfully yet and would be most appreciative if anyone has done or can work out how to do it.
the main problem is that you seek to define column names in data (normally the job of syntax).
If the data volume allows you to read it all twice, then
1 first pass
read through the file, collecting names and expected widths
2 second pass
read event data into relevant names
How big is your data file?
How often is it refreshed and needing to be reloaded?
Thank you for your comments on this, they were a massive help. I considered the TRANSPOSE but didn't realize it would be able to do it. I've managed to do it although I simplified the data above so a bit more has gone into it.
The same fields within the EVENT_DATA could appear against different EVENT_IDs. In addition, as with the last observation here, event4, the EVENT_DATA field can contain multiple fields.
I basically used the logic you posted in the T2 dataset above to parse out each variable but then put it into a DO UNTIL loop which cut down the EVENT_ID each iteration and output observations within the loop thus the resultant dataset for the above data had 5 observations because event 4 was repeated with the second variable in the EVENT_ID.
I then simply used the TRANSPOSE procedure using ID as FIELD, VAR as VALUE and did it BY the EVENT_ID.