topic Re: Importing a csv file with contains a variable with a long string in SAS Programming

Importing a csv file with contains a variable with a long string

75063 — Wed, 01 Aug 2018 06:19:50 GMT

I am using SAS on demand for academics and want to import a CSV file with 200 variables and 350 observations.

I imported by using the code -

filename tiger '/home/user/username/State.csv';
Proc Import datafile= tiger DBMS = csv OUT=libname.Statea ;
GUESSINGROWS= All;
run;

A sample of the csv is -

4	3
2	2	The career services office was not very helpful. I talked with them a few timesdo, not for those who want because all they did was tell me stuff I already knew and then said "you know what you want, now just go find it." I would have thought that they would be more helpful in actually finding a job. help actually finding a job. and then gave up They seem to be an office for those who have no idea what they want to

when the csv file was read in sas the contents of the third column extended to the other columns - just as -

3	7
The career services office was not very helpful. I talked with them a few timesdo, not for those who	stuff I already knew and then said "you know w	T want because all they did was tell me hat you want, now just go find it." I would have thought that they would be more helpful in actually finding a job. help actually finding a job. and then gave up They seem to be an office for those who have no idea what they want to

Can someone please suggest how to read the CSV file in sas using "SAS on demand for academics" having a variable with a long string as shown above.

Thank you!

Re: Importing a csv file with contains a variable with a long string

andreas_lds — Wed, 01 Aug 2018 06:42:21 GMT

This does not look like a csv-file! Please open the file with a text editor and post some lines using "insert sas code" or the {i}-icon

Re: Importing a csv file with contains a variable with a long string

Kurt_Bremser — Wed, 01 Aug 2018 06:46:23 GMT

Your sample does not show a csv file, as a csv file is text with the columns separated by commas. Please post an actual sample of the csv as attachment or into a {i} window.

Note that you have commas and quotes in your texts, so this will make it very hard for SAS to make any sense out of it.

I strongly recommend to not use proc import for data like this, but roll your own data step. We may be able to help you once we have a good example of your input data.

Re: Importing a csv file with contains a variable with a long string

75063 — Wed, 01 Aug 2018 06:49:42 GMT

The original file was an xlsx file which I saved as a CSV file. The issue
that i am facing is that the long string is being read into multiple
observation leading to erroneous results

Re: Importing a csv file with contains a variable with a long string

Kurt_Bremser — Wed, 01 Aug 2018 06:52:00 GMT

@75063 wrote:
The original file was an xlsx file which I saved as a CSV file. The issue
that i am facing is that the long string is being read into multiple
observation leading to erroneous results

OK, and where is the example? We can't help you without actual data to test code against.

Re: Importing a csv file with contains a variable with a long string

RW9 — Wed, 01 Aug 2018 08:01:10 GMT

Whilst I agree with all posted before, and that what is posted is not sufficient to really post any further help, I would also ask, what do you intend to do with this data? A big block of text is pretty much useless to anyone, next to impossible to process in any meaningful way. So maybe just drop it?

Re: Importing a csv file with contains a variable with a long string

ballardw — Wed, 01 Aug 2018 14:58:08 GMT

@75063 wrote:
The original file was an xlsx file which I saved as a CSV file. The issue
that i am facing is that the long string is being read into multiple
observation leading to erroneous results

If at all practical I would see if this Excel file was exported from some other application. If so, see if that source could create a delimited file other than comma, perhaps a pipe character, |, and import that. The mixture of quotes and commas imbedded in your text makes this a very poor candidate for CSV.

Or perhaps save as simple open xml and use the SAS XML mapper to attempt to read the file.