topic Re: Import not cleaned text string column into a dataset in SAS Programming

Import not cleaned text string column into a dataset

kumarK — Tue, 13 Sep 2016 23:58:03 GMT

Hi,

How to import special characters (HIdden quotes,double quotes, next line) string around 2000 length in one of the column in csv file.(attached)

Cust_id	trans_type	gender	trans_date	trans_amt	Comments
11201	Entertainment	F	########	$50.65	I Talked to customer, but he is not interested
11205	Grocery	m	########	$90.00	1. he is not wiling but he is will continue in future 2. he is good
11206	Travel	m	########	$23.00	"not wiiling to continue" regards Diip
11206	Travel	m	########	$23.00	"not talked @@ regards hiip

Re: Import not cleaned text string column into a dataset

Reeza — Wed, 14 Sep 2016 00:10:54 GMT

Yuck.

This is one of the few cases where I say keep the data in Excel since it will keep,relevant content in a single cell.

Note this assumes you started with an Excel file since going to Excel from a CSV will have the same issues.

Re: Import not cleaned text string column into a dataset

kumarK — Wed, 14 Sep 2016 00:36:52 GMT

unfortunately we dont have access to import excel files. we need to convert either csv or text file.

this is just sample data i have unstructured data with so much cooments wecan't controal that column..i need to load this data into a dataset.do you have any recommendations how can i import?

Re: Import not cleaned text string column into a dataset

Ksharp — Wed, 14 Sep 2016 02:03:53 GMT

You are luck. The termstr is CRLF . Others are CR . after I checked by NotePad++ .




filename x '/folders/myfolders/comments.csv'  termstr=crlf;
proc import datafile=x out=want dbms=csv replace;run;

Re: Import not cleaned text string column into a dataset

Ksharp — Wed, 14 Sep 2016 02:04:41 GMT

You are luck. The termstr is CRLF . Others are CR . after I checked by NotePad++ .




filename x '/folders/myfolders/comments.csv'  termstr=crlf;
proc import datafile=x out=want dbms=csv replace;
 guessingrows=32767;
run;

Re: Import not cleaned text string column into a dataset

Tom — Wed, 14 Sep 2016 02:07:57 GMT

I don't see any attached file.

Why do you say that you cannot read the Excel file? SAS can directly read both XLS and XLSX files.

libname mydata xlsx 'myfile.xlsx';
proc copy inlib=mydata outlib=work;
run;

If you have a CSV file generated by Excel then you can probably read it into SAS by making sure to tell SAS to use CRLF as the end of line markers. That way it will read the embedded CR that Excel normally uses for line breaks inside of cells.

data want ;
  infile 'myfile.csv' dsd truncover lrecl=100000 truncover firstobs=2 termstr=crlf;
  length col1-col6 $32767;
  input col1-col6;
run;

Re: Import not cleaned text string column into a dataset

ScottBass — Wed, 14 Sep 2016 02:19:20 GMT

Hi,

I created the attached txt file, simulating an export from Excel to CSV.

I imported it with this code:

data test;
   infile "\\UNC path to my file\test.txt" delimiter="09"x termstr="|" dsd;
   length col1 col2 $1000;
   input col1 col2;
run;
proc print;
run;

If your data has embedded CRLF, then use termstr to change the character that SAS thinks marks the end-of-record.

Note: usually Excel doesn't use CRLF to embed line feeds, but only LF (I think it's "0A"x). So, you may not have to use termstr= or the pipe symbol to mark your end-of-record.

I have a Powershell script which runs a query against an Excel file and returns it as a CSV stream. You can use an infile pipe to dynamically read the stream in a data step.

You could modify it to write out your custom termstr character at the end of each line (something like SELECT [*,"|"] FROM [Sheet1$]).

Or, use another technique, such as adding the end-of-record character in your Excel file itself.

If you want to try the Powershell script, I don't have time to support further questions, but Google and the SAS documentation are your friend.

Here's the link: https://github.com/scottbass/Powershell/blob/master/Scripts/Query-Excel.ps1

Hope this helps...

Re: Import not cleaned text string column into a dataset

kumarK — Thu, 15 Sep 2016 00:51:21 GMT

i have successfully able to import the file. now i want to strip the words in text. below code works fine for small datset.

however i have large datset it's taking too much time.. anyone have any suggestions to improve the code. Thanks

data have1;
set have(keep=comments);
count =countw(comments," ");
run;

data have2(drop=count i);
set have1;
do i=1 to count;
words = scan(comments,i ," ");
output;
end;
run;

Re: Import not cleaned text string column into a dataset

Ksharp — Thu, 15 Sep 2016 02:24:52 GMT

You can combine these two data step into one :


 
data have2(drop=count i);
set have;
do i=1 to countw(comments," ");
words = scan(comments,i ," ");
output;
end;
run;

Re: Import not cleaned text string column into a dataset

kumarK — Thu, 15 Sep 2016 17:29:05 GMT

This is not reducing my run time.

Can we write a similar program using PRX fncitons. that would help in running time?

Re: Import not cleaned text string column into a dataset

Ksharp — Fri, 16 Sep 2016 01:50:21 GMT

I don't think PRX functions could reduce your time. The fast efficient way I could think is using IML code. But that need lots of memory if you have big table.

Re: Import not cleaned text string column into a dataset

Reeza — Fri, 16 Sep 2016 02:57:29 GMT

Define takes too much time?

Is this a process that needs to be repeated every week? Month?

Re: Import not cleaned text string column into a dataset

Ksharp — Fri, 16 Sep 2016 03:28:29 GMT

Define a LENGTH statement might save you some time.

data have2(drop=count i);
set have;
LENGTH WORDS $ 40;
do i=1 to countw(comments," ");
words = scan(comments,i ," ");
output;
end;
run;

Re: Import not cleaned text string column into a dataset

ScottBass — Fri, 16 Sep 2016 04:49:07 GMT

@kumarK wrote:
i have successfully able to import the file. now i want to strip the words in text. below code works fine for small datset.

however i have large datset it's taking too much time.. anyone have any suggestions to improve the code. Thanks

data have1;
set have(keep=comments);
count =countw(comments," ");
run;

data have2(drop=count i);
set have1;
do i=1 to count;
words = scan(comments,i ," ");
output;
end;
run;

Well, sure, there's plenty of ways to make this run faster:

Get a faster server
Use MP_Connect to process your data in multiple SAS processes
If you're using EG, use EG's parallel processing to process your data in multiple SAS processes
Use PROC DS2's threading capability to process your data in multiple threads
As @Ksharp said, don't do a double pass over your data. Your first step reads comments into the PDV - do your processing on it then.
You might get a slight performance gain by using the SPDE engine for your source and target datasets.

However, it would be a huge help to know WHY you're doing what your doing? Clearly, you're transforming your data, writing each word of the comments field as a separate record in your final output table, where "word" is a token delimited by a space.

What do you then want to do with the data? If we knew that, perhaps there would be other approaches that wouldn't involve parsing your comments field into one record per word. For example, if you need to get a count of each word, you could use a hash object to sum for each word, and your final dataset (and therefore I/O) would be much smaller.

If your source data has 1M records, and your comments field averages 100 words, you've just converted your 1M records into 100M.

For small datasets, inefficient approaches are ok - quick and dirty rules the day. But, if your data is "large", and you're experiencing performance issues, you may need to apply more creative approaches.

So, forget the technicalities...what do you want to DO?

Re: Import not cleaned text string column into a dataset

kumarK — Sat, 17 Sep 2016 16:09:25 GMT

the dataset i have around 500k comments...i want to strip the comments into separate words..then i need to count how many times each word apperead. the code used above is taking too much time and generating around 100gb data..so i want to reduce the time and space.

after some research i came to know that i have intially assigend length for coments variable $5000 so that all comments is loaded proerly into my dataset. i think this is taking my dataset around 100gb

below is the the output i am getting after running the code, lf you see for every word its taking comments ..is there anyway we can improve the code. or how can i know max length of the variable before loading to dataset so that i can assign that length to the variable? or can i get the similar results another way? I hope you understand the issue. Thanks

comments	words
I Talked to customer	I
I Talked to customer	Talked
I Talked to customer	to
I Talked to customer	customer

Re: Import not cleaned text string column into a dataset

kumarK — Sat, 17 Sep 2016 16:15:28 GMT

"What do you then want to do with the data? If we knew that, perhaps there would be other approaches that wouldn't involve parsing your comments field into one record per word. For example, if you need to get a count of each word, you could use a hash object to sum for each word, and your final dataset (and therefore I/O) would be much smaller.

If your source data has 1M records, and your comments field averages 100 words, you've just converted your 1M records into 100M."

Exactly you got my problem. the program i written unnessarly creating 100M dataset like the above example.

My objective is to to get a count of each word in comments variable.

"you could use a hash object to sum for each word, and your final dataset (and therefore I/O) would be much smaller." -- can you please help me with code how we can get this using hash object.

Re: Import not cleaned text string column into a dataset

Tom — Sat, 17 Sep 2016 19:05:01 GMT

Try using a data step view to split the words. Then at least you do not need to save the table.

data words / view=words ;
  set have (keep=comment);
  do i=1 to countw(comment,' ');
     length word $100 ;
     word = scan(comment,i,' ');
     output;
  end;
  keep word ;
run;
proc summary nway data=words;
   class word ;
   output out=word_freqs ;
run;

Re: Import not cleaned text string column into a dataset

ScottBass — Sun, 18 Sep 2016 09:13:25 GMT

@kumarK wrote:
"you could use a hash object to sum for each word, and your final dataset (and therefore I/O) would be much smaller." -- can you please help me with code how we can get this using hash object.

I don't have time right now to write example code, but the logic would go like this:

You would have a data _null_ step

Your set statement would have "end=eof"

Create a hash object when _n_=1

The hash object key is "word", the hash object var is "count"

Parse your words as you are doing now

I honestly can't remember if the keys in a hash object are case sensitive, and I'm not in front of SAS right now to test. If so, convert the case of your words.

For each word (i.e. within your parsing loop):

Explicitly set count to missing

Find the existing word in your hash object

If the find is successful, count will be set to the current count from the hash object

If the find is unsuccessful, count will remain missing

Set count=sum(count,1). This will set count to either 1, or count+1

Update the hash object. This will either update the existing word, or add the new word

When end=eof, write the hash object to an output dataset

Read the SAS doc on the hash object: http://support.sas.com/documentation/cdl/en/lecompobjref/67221/HTML/default/viewer.htm#p0zjo5imt7rb9rn1npxm1rsl58b3.htm

See the Add, Declare, Find, Output, Replace, and Sum methods. See the examples. If you don't understand the examples, run them until you do. They can be confusing when you're first learning hash objects, but once you understand them, they are a powerful tool in your SAS kit bag.

I know the above approach would work. However, the Sum method may be a an alternative approach, which may also work for you.

HTH...

Edit: See http://support.sas.com/documentation/cdl/en/lecompobjref/67221/HTML/default/viewer.htm#p00ilfw5pzcjvtn1nfya9863fozd.htm (Example 5). This should solve your your issue.

Edit #2: But you should also try Tom's approach and compare the performance. If his approach performs well for you, it would be a simpler approach requiring less code. Regardless of your final approach, use this as an opportunity to learn more about hash objects.

Re: Import not cleaned text string column into a dataset

Reeza — Sat, 17 Sep 2016 22:55:43 GMT

Have you considered SAS text analytics rather than using Base SAS?

Re: Import not cleaned text string column into a dataset

kumarK — Sun, 18 Sep 2016 03:32:36 GMT

dont have license to SAS text analytics 😞