01-04-2013 02:05 PM
I have a dataset that i need to import into SAS. I can perform the actual import easy enough. I have no idea what to do with the data. Apparently, from looking closely at the data, it showed up into 6 rows for each record. The first 5 columns have 1 piece of the record while the remaining have the rest of the data.
I tried to search the community but don't really know the terminology for this. Can anyone help me figure out how to get all 6 rows down to one row? I am not new to SAS, however, I am not an advanced user by any means. I am not even sure of the terminology behind what i am seeing here. If anyone can help me do this...i would be very appreciative.
01-04-2013 02:30 PM
The problem is that there are no duplicates. I have dropped the results into excel to show you exactly the type of data i am getting. I wish i could use a nondup key to get this to work.
It's putting part of the data on a different observation. I gave you three examples... there are hundreds more in my dataset.. but you will get the idea when you see it. The first 5 rows have a different piece of the observation, while the 6th row has the numeric values all the way through. Its frustrating.
|1/3/2013 12:00:00 AM||15679|
|1/3/2013 12:00:00 AM||15679|
|1/3/2013 12:00:00 AM||14571|
01-04-2013 02:35 PM
To me, it looks like something funky with the delimiters, perhaps? Is this a CSV or tab-delimited file? Double-check the settings that EG is using for the import and make sure it matches what you expect.
If you could supply even just a bit of the raw file, it would be easier to help (though I understand if you cannot share...)
01-04-2013 02:46 PM
01-08-2013 04:58 PM
Assuming that what you attached is a sample of the raw data that you must import, I think your approach will need to use DATA step to read the field values into the correct columns somewhat manually. You may have to play with using multiple INPUT statements per "record" as they seem to span multiple lines. See some doc at:
01-08-2013 05:01 PM
Your file supplied/attached is definitely not comma delimited.
You also have multiple column names that are identical so that will be an issue.
Can you supply the CSV file without the 'extra' spaces?
2 or 3 rows in the exact format is helpfult
01-08-2013 07:23 PM
I re-looked at my file and I had not uploaded an example of a comma deliminated file. Our system only allows to download as a text file. The same results happen though when i put it into excel.
I think Chris had the right idea of making inputs... however... I am not at all an expert in SQL. I learned on the fly out of necessity and have not delve into it like i have wanted to. I am way over my head on this one.
01-08-2013 08:30 PM
You wouldn't use SQL for this, you would use a data step, but it would be a brute force method, and from what I see there's no way to determine which values align with which variable names. How would you align them?
01-08-2013 09:02 PM
I know what needs to happen, but I am not sure how to do any of this at the moment and since I really don't have any more hours to figure it out... i am going to align it in excel... it'll be much faster... then i can just import it to SAS in the way it is supposed to be viewed. I will figure out how to do this later.
Thanks for your help!