Desktop productivity for business analysts and programmers

SAS EG 4.1 Import Help

Reply
Contributor
Posts: 70

SAS EG 4.1 Import Help

Hey Everyone!

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.

Contributor
Posts: 62

Re: SAS EG 4.1 Import Help

One way for deleting duplicate values is proc sort and "nodupkey" option.

for example:

PROC SORT data=sample nodupkey;

BY id;

RUN;

for details, see this.

Contributor
Posts: 70

Re: SAS EG 4.1 Import Help

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.  Smiley Happy 

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.

                        A/A A/AA/AA/AA/AA/AAFTERAFTERAFTERCEXPCEXPCEXPCSIPCSIPCSIPGRAMGRAMGRAMGREETGREETGREETIMPIMPIMPPR/CMPR/CMPR/CMPROCDPROCDPROCDS.P.KS.P.KS.P.KSENDSENDSENDSOLDSOLDSOLDSV/CCSV/CCSV/CCTECHTECHTECHTL/RJTL/RJTL/RJ
    ICST                                                                                                                                                                                                                             
DATA                                                                                                                                                                                                                                
       John Smith                                                                                                                                                                                                            
    1/3/2013 12:00:00 AM   15679                                                                                                                                                                                                                            
                        15680                                                                                                                                                                                                           
                           100                                                                                                                                                                                                        
    ICST                                                                                                                                                                                                                             
DATA                                                                                                                                                                                                                                
       John Smith                                                                                                                                                                                                            
    1/3/2013 12:00:00 AM   15679                                                                                                                                                                                                                            
                        15680                                                                                                                                                                                                           
                           100                                                                                                                                                                                                        
    ICST                                                                                                                                                                                                                             
DATA                                                                                                                                                                                                                                
       John Smith                                                                                                                                                                                                            
    1/3/2013 12:00:00 AM   14571                                                                                                                                                                                                                            
                        15680                                                                                                                                                                                                           
                           90                                                                                                                                                                                                        
Community Manager
Posts: 2,889

Re: SAS EG 4.1 Import Help

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...)

Chris

Contributor
Posts: 70

Re: SAS EG 4.1 Import Help

I attached a sample of the raw data.  Because there was so much i deleted some of the data which left a couple of extra spaces between a couple of the segments... those extra spaces are not there on the original data.  The data is comma deliminated.

Any help at all is so much appreciated.

Dean

Attachment
Contributor
Posts: 70

Re: SAS EG 4.1 Import Help

Anyone? LOL... i have still not been able to solve this.  I am sure there is something that can be done to sort the data correctly when importing it into SAS.

Dean

Community Manager
Posts: 2,889

Re: SAS EG 4.1 Import Help

Dean,

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:

Step-by-Step Programming with Base SAS(R) Software

Chris

Super User
Posts: 19,171

Re: SAS EG 4.1 Import Help

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

Contributor
Posts: 70

Re: SAS EG 4.1 Import Help

Reeza,

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.

Dean

Attachment
Super User
Posts: 19,171

Re: SAS EG 4.1 Import Help

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?

Contributor
Posts: 70

Re: SAS EG 4.1 Import Help

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!

Dean

Ask a Question
Discussion stats
  • 10 replies
  • 267 views
  • 0 likes
  • 4 in conversation