topic Re: creating Delimited file in SAS Programming

creating Delimited file

skygold16 — Tue, 03 Dec 2013 12:54:08 GMT

Hey Guys,

Another challenging stuff....

I have few columns in my SAS dataset, and value is something like...

'<! 2013-11-28 09:54:29 Escalated >

Entered on 2013-11-28 at 09:54:26 by XYZ ABC :

Approved Incident report attached.

/XYZ'

Now when I put this string in output file with dlm=',', output gets jumbled up.

I have tried using string=compbl(string); but that didn't help since its a newline character which I want to replace with space in my output file.

I also tried using column_name1 = COMPRESS(column_name,' ',"kw");

This worked to remove new line character, but it doesn't replace new line character with space.

So output will look something like....

'<! 2013-11-28 09:54:29 Escalated >Entered on 2013-11-28 at 09:54:26 by XYZ ABC :Approved Incident report attached./XYZ'

I just want space wherever we have new line.

Also for few values, i see double quote appearing at the start of string, but if I open same file in excel, I don't see. and most veird thing is, its only appearing for few text columns and not for all .

Any solutions?

Re: creating Delimited file

data_null__ — Tue, 03 Dec 2013 13:15:01 GMT

It is not at all clear to me what your problem is. However you might want to look at the Web Tools functions

SAS(R) 9.4 Language Elements by Name, Product, and Category

Re: creating Delimited file

skygold16 — Tue, 03 Dec 2013 13:58:42 GMT

Hi,

I have text column having value like below:

'<! 2013-11-28 09:54:29 Escalated >

Entered on 2013-11-28 at 09:54:26 by XYZ ABC :

Approved Incident report attached.

/XYZ'

The above text is part of single column value.

I am trying to put this in csv file but when I do so, using column_name1 = COMPRESS(column_name,' ',"kw"); I see double quote for few of the text column

"<! 2013-11-28 09:54:29 Escalated >Entered on 2013-11-28 at 09:54:26 by XYZ ABC :Approved Incident report attached./XYZ"

where it shouldn't since input column doesn't have any quotations.

I have attached reference images in my previous post.

Re: creating Delimited file

data_null__ — Tue, 03 Dec 2013 16:20:49 GMT

I think you must be using a delimiter that is one of the characters in the string. See this example what I think models your problem. However if that is indeed what is happening you want then quotes otherwise you won't be able to read it.

18         data _null_;
19            file log ls=256 dsd dlm='#'; 
20            length a b $200; 
21            a=catx('0a'x,'<! 2013-11-28 09:54:29 Escalated >',
22               'Entered on 2013-11-28 at 09&#58;54&#58;26 by XYZ ABC &#58;',
23               'Approved Incident report attached.',
24               '/XYZ');
25            b=compress(a,' ','KW');
26            put b;
27            run;

"<! 2013-11-28 09:54:29 Escalated >Entered on 2013-11-28 at 09&#58;54&#58;26 by XYZ ABC &#58;Approved Incident report attached./XYZ"

Re: creating Delimited file

skygold16 — Wed, 04 Dec 2013 06:28:39 GMT

Hi, I am using comma "," as delimiter in my output csv file and i have this sample value in entire column. Above one of the value i have specified here just for a reference. So I am not sure where i will have new line character in my column .

Re: creating Delimited file

Patrick — Wed, 04 Dec 2013 10:16:15 GMT

Why don't you attach some sample data and then tell us how the result should look like? That would make it much clearer and I'm sure someone as senior as data _null_ would be able to provide you with a perfect solution for the challenge.

Re: creating Delimited file

skygold16 — Wed, 04 Dec 2013 10:25:37 GMT

Unfortunately its client data and I will not be able to do so.

Here is the sample data:

'<! 2013-11-28 09:54:29 Escalated >

Entered on 2013-11-28 at 09:54:26 by XYZ ABC :

Approved Incident report attached.

/XYZ'

Result Should be :

'<! 2013-11-28 09:54:29 Escalated > Entered on 2013-11-28 at 09:54:26 by XYZ ABC : Approved Incident report attached. /XYZ'

This is happening for some columns properly but for few its displays double quote as you can see in attached images from my first post.

Re: creating Delimited file

Patrick — Wed, 04 Dec 2013 10:33:37 GMT

You tell us that you're dealing with line feeds. If so then you should attach some "real" data as we won't get this challenge if you simply copy/paste it into the forum. I believe for issues like this we need an actual file as an attachment.

You can have such data if you store an Excel file as .csv and there were line feeds in some of the cells. TERMSTR=CRLF in the INFILE statement is eventually what you're after to tell SAS that LF alone is not an end-of-line indicator.

It would be much easier to give you advice by having some real data.

Re: creating Delimited file

skygold16 — Wed, 04 Dec 2013 10:39:59 GMT

Yeah, but that is not possible here....still i have copied few line below how do isee in my output file

in,"<! 2013-11-28 08:57.........

....

"Incid......

....

elrock,"<! 2....

Re: creating Delimited file

Patrick — Wed, 04 Dec 2013 10:42:20 GMT

Why can't you just attach 2 lines of your data - whether that's now a SAS file or some external data?

Re: creating Delimited file

skygold16 — Wed, 04 Dec 2013 11:22:52 GMT

its normal .csv file

Headers:

eventShortDesc,eventDesc,SummaryDescription,sourceSystemCd,sourceSystemCd,legalOrgId,sourceSystemCd,managementOrgId,sourceSystemCd,geographyId,x_footprintsID,x_severity_class,x_fp_category,x_fp_coreSystem,x_fp_subSystem,x_fp_closureCd,x_fp_systemNm,x_fp_firstNm,x_fp_lastNm,x_fp_userID,x_fp_email,x_fp_department,x_fp_office,x_fp_area,x_fp_resolution,x_submission_dttm,x_closure_dttm

Line 1:

**** Expert log in,"<! 2013-11-28 08:57:53 Escalated >Entered on 2013-11-28 at 08:57:53 by ***** Jern :Entered on 27/11/2013 at 17:24:59 CET (GMT+0100) by **** Service Desk:Hi,We communicated to **** Widros the solution of the problem:WAS lost contact to repository in ***. *** sorted out hte storage problem and that solved the problem.Thanks,FrancoEntered on 2013-09-05 at 13:46:51 by ***** Curman :Roll back of a change at CGI resolved the problem. Verified by *** Wennerholm and Telrock.BR/****",Falcon Expert log in,MON,MON,-1,MON,-1,MON,-1,,3,Core__bSystems,,,,,,,,,ECS - Customer Service Area,,,"Entered on 2013-11-28 at 08:57:53 by ***** Jern :Entered on 27/11/2013 at 17:24:59 CET (GMT+0100) by **** Service Desk:Hi,We communicated to **** Widros the solution of the problem:WAS lost contact to repository in ****. ****sorted out hte storage problem and that solved the problem. Thanks,FrancoEntered on 2013-09-05 at 13:46:51 by **** Curman",03SEP13:14:08:42,

Line 2:

"Incident SB1 **** , compliance breach.",<! 2013-11-01 13:28:00 groe >Copied to Change Management as RFC #3956 by Gro **** ,"Incident SB1 ****, compliance breach.",MON,MON,-1,MON,-1,MON,-1,,3,Core__bSystems,,,,,,,,,Customer Service Bank Market,,***NO,"Entered on 2013-11-27 at 11:47:26 by Gro **** :Implemented (RFC 3956).Entered on 2013-10-03 at 11:54:17 by **** Service Desk:Hi,Since I am not getting any response, I am closing this ticket as resolved. Please reopen the ticket in case of any clarifications.Regards,**** M",25SEP13:09:30:17,

in line 1, just after first comma, second column data starts with ", but in actual dataset it is not like that....there is no quotation mark.

Sama for line 2, first data starts with ".

Re: creating Delimited file

data_null__ — Wed, 04 Dec 2013 12:22:21 GMT

The field contains a comma and it was quoted when written. If you want normal csv that's what you got.

Re: creating Delimited file

skygold16 — Wed, 04 Dec 2013 12:53:33 GMT

Aah, I understand, but is there any solution to remove that?

I am using DLM = ',' and DSD as well.

FILE "&PATH.\&OUTPUT..csv" ENCODING="ASCII" DLM=',' DSD LRECL=32767 DROPOVER;

Do you suggest anything else?

Re: creating Delimited file

data_null__ — Wed, 04 Dec 2013 13:07:47 GMT

If you don't have quotes around delimiter characters that are not delimiters you cannot read the csv, because the commas and the delimiters (a comma) are confounded. You need to accept what you have (the proper CSV) or remove the commas from the text (silly idea) or change the delimiter to something that is never part of the text.