topic Re: SAS EG 4.1 Import Help in SAS Enterprise Guide

SAS EG 4.1 Import Help

D_Z_ — Fri, 04 Jan 2013 19:05:11 GMT

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.

Re: SAS EG 4.1 Import Help

MohammadFayaz — Fri, 04 Jan 2013 19:18:48 GMT

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.

Re: SAS EG 4.1 Import Help

D_Z_ — Fri, 04 Jan 2013 19:30:47 GMT

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.

A/A

AFTER

CEXP

CSIP

GRAM

GREET

IMP

PR/CM

PROCD

S.P.K

SEND

SOLD

SV/CC

TECH

TL/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

Re: SAS EG 4.1 Import Help

ChrisHemedinger — Fri, 04 Jan 2013 19:35:57 GMT

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

Re: SAS EG 4.1 Import Help

D_Z_ — Fri, 04 Jan 2013 19:46:11 GMT

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

Re: SAS EG 4.1 Import Help

D_Z_ — Tue, 08 Jan 2013 21:44:51 GMT

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

Re: SAS EG 4.1 Import Help

ChrisHemedinger — Tue, 08 Jan 2013 21:58:59 GMT

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

Re: SAS EG 4.1 Import Help

Reeza — Tue, 08 Jan 2013 22:01:36 GMT

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

Re: SAS EG 4.1 Import Help

D_Z_ — Wed, 09 Jan 2013 00:23:32 GMT

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

Re: SAS EG 4.1 Import Help

Reeza — Wed, 09 Jan 2013 01:30:46 GMT

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?

Re: SAS EG 4.1 Import Help

D_Z_ — Wed, 09 Jan 2013 02:02:21 GMT

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