topic Re: Concantenating with a Carriage Return instead of Space in SAS Programming

Concantenating with a Carriage Return instead of Space

D_Z_ — Mon, 06 Jan 2020 18:20:52 GMT

The code is below. When the data gets concatenated the way it works right now is that it puts a space in between each line its concentrating. what i would like it to do is put a carriage return instead. Can anyone help me with this modification. I have not found anything (prob because i couldn't determine the right search words) that will do this for me.

data  work.order_notes;
length order_notes $ 10000 /* need to be the max */;
     set sorted;
     by odr_odr_number dt_crtd;
     retain order_notes;
     if first.odr_odr_number then order_notes = scratch_pad;
     else order_notes=trim(order_notes) || ' ' || trim(scratch_pad);
     DROP SCRATCH_PAD;
     if last.odr_odr_number then output;
run;

thanks to anyone that can help me with this one.

Dean

Re: Concantenating with a Carriage Return instead of Space

DWilson — Mon, 06 Jan 2020 19:45:57 GMT

@D_Z_ wrote:
The code is below. When the data gets concatenated the way it works right now is that it puts a space in between each line its concentrating. what i would like it to do is put a carriage return instead. Can anyone help me with this modification. I have not found anything (prob because i couldn't determine the right search words) that will do this for me.
data  work.order_notes;
length order_notes $ 10000 /* need to be the max */;
     set sorted;
     by odr_odr_number dt_crtd;
     retain order_notes;
     if first.odr_odr_number then order_notes = scratch_pad;
     else order_notes=trim(order_notes) || ' ' || trim(scratch_pad);
     DROP SCRATCH_PAD;
     if last.odr_odr_number then output;
run;
thanks to anyone that can help me with this one.

Dean

Well, on windows:

0x0D is hexadecimal for carriage return

0x0A is hex for line feed

0x0D0A is hex for carriage return followed by line feed

You can used one of these hex values as the separator instead of a space but you are creating a character variable in a SAS data set so I'm not sure how this helps you.

order_notes=trim(order_notes) || '0x0D0A' || trim(scratch_pad);

Hrm, you might need to tell SAS that 0x0D0A is hex, maybe like this:

order_notes=trim(order_notes) || '0x0D0A'x || trim(scratch_pad);

Re: Concantenating with a Carriage Return instead of Space

jhammouda — Mon, 06 Jan 2020 19:46:03 GMT

Hi D_Z,

replace ' ' in the concatenation with the hex code for CR '0D'x

Re: Concantenating with a Carriage Return instead of Space

ErikLund_Jensen — Mon, 06 Jan 2020 20:27:14 GMT

Hi @D_Z_

As @DWilson suggests, use a hex constant in the concatenation. But use only '0D0A'x for a Windows line break, or '0A'x for a Linux line break. Don't use '0x0D0A or '0x0A' because '0x' is not a hex character code and causes an error in SAS. Valid hex codes are 00-FF.

But I wonder why you would want those line breaks. Normally one would do a lot to get rid of them, because they have no meaning inside SAS and isnt' shown in a SAS table view. They may cause a lot of trouble when they come out of SAS. They will generate line breaks If data is written to (say) a CSV file, so the file is corrupt afterwards with broken records. They don't do any harm in export to Excel, but they don't work either, they just disappear.

Re: Concantenating with a Carriage Return instead of Space

kovamk — Thu, 02 Feb 2023 22:43:56 GMT

Hi Erik, so are you saying when using sas to export data into excel, you can't use a carriage return to have the data come out in 2 rows in excel? i.e. I'm trying to get this wording split into 2 lines in excel:

LABEL HEDIS_COLON_GAP = 'Colorectal Cancer Screening (Display for MA)' ;

I'd like the final result to have 'Colorectal Cancer Screening' on the first line in the cell and (Display for MA) on the second line in the cell.

Colorectal Cancer Screening

(Display for MA)

But I can't get it to work using any of the code that has been suggested online. I either get an error or the code doesn't work.

Thanks,
Mike

Re: Concantenating with a Carriage Return instead of Space

ErikLund_Jensen — Fri, 03 Feb 2023 08:54:53 GMT

Hi @kovamk

Yes. At least I never got it to work. I remember a Stackowerflow post that addressed the problem, and it said something about the code actually being there, but not working until F2 is pressed in every cell, so to me that equals "not working".

I found a paper with a receipe to use ODS / proc print with a tagset to cheate the excel table. I haven´t tried it, but it is worth exploring:

https://www.pharmasug.org/proceedings/2014/CC/PharmaSUG-2014-CC07.pdf