BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
skygold16
Fluorite | Level 6

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&#58;54&#58;26 by XYZ ABC &#58;

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&#58;54&#58;26 by XYZ ABC &#58;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?


q2.PNGQ.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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.

View solution in original post

13 REPLIES 13
data_null__
Jade | Level 19

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

skygold16
Fluorite | Level 6

Hi,

I have text column having value like below:

'<! 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'


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&#58;54&#58;26 by XYZ ABC &#58;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.

data_null__
Jade | Level 19

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"
skygold16
Fluorite | Level 6

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 .

Patrick
Opal | Level 21

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.

skygold16
Fluorite | Level 6

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&#58;54&#58;26 by XYZ ABC &#58;

Approved Incident report attached.

/XYZ'



Result Should be :

'<! 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'


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.




Patrick
Opal | Level 21

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.

skygold16
Fluorite | Level 6

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

Patrick
Opal | Level 21

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

skygold16
Fluorite | Level 6

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&#58;57&#58;53 by ***** Jern &#58;Entered on 27/11/2013 at 17&#58;24&#58;59 CET (GMT+0100) by **** Service Desk&#58;Hi,We communicated to **** Widros the solution of the problem&#58;WAS lost contact to repository in ***. *** sorted out hte storage problem and that solved the problem.Thanks,FrancoEntered on 2013-09-05 at 13&#58;46&#58;51 by ***** Curman &#58;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&#58;57&#58;53 by ***** Jern &#58;Entered on 27/11/2013 at 17&#58;24&#58;59 CET (GMT+0100) by **** Service Desk&#58;Hi,We communicated to **** Widros the solution of the problem&#58;WAS lost contact to repository in ****. ****sorted out hte storage problem and that solved the problem. Thanks,FrancoEntered on 2013-09-05 at 13&#58;46&#58;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&#58;47&#58;26 by Gro **** &#58;Implemented (RFC 3956).Entered on 2013-10-03 at 11&#58;54&#58;17 by **** Service Desk&#58;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 ".

data_null__
Jade | Level 19

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

skygold16
Fluorite | Level 6

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?

data_null__
Jade | Level 19

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1413 views
  • 3 likes
  • 3 in conversation