Adding leading blanks.

[deleted_user]

I have a character variable, Var1. I want to add 5 leading blanks, before using DDE to write the variable to Microsoft Excel.

This doesn't work: Var1 = "bbbbb" || Var1, where each b stands for a blank sign.

How can I do it?

It's important for me to transfer the variable with leading blanks. I don't want to adjust in the Excel cells after transferring the variable (without leading blanks).

Susan

[deleted_user]

I can't get

put '[alignment(?,?,?,?,?)]';

to work either.

Any ideas?

Susan

[sbb]

First, the length of the SAS character type variable must be able to accommodate the additional characters, otherwise when you concatenate characters to the beginning of a SAS variable, the characters that follow will be truncated (without any warning). A SAS LENGTH statement must appear in your SAS DATA step, and if you have a SAS input file being used, the LENGTH statement must be declared before the SET statement.

You may want to explain exactly what does happen with your attempts, rather than simply stating that it does not work. With this additional information you may find more useful feedback from forum subscribers.

Also, I suggest adding some SAS diagnostic commands such as:

PUT '>diag>' / _ALL_;

And also consider using a SAS format that may help you determine the SAS variable contents, such as $HEXnn. in a PUT statement, as well.

Scott Barry
SBBWorks, Inc.

[deleted_user]

I have the LENGTH set to 35 and Var1:s longest string have 21 characters.

I checked my SAS-table after using the code Var1 = "bbbbb" || Var1, where each b stands for a blank sign.
The code worked; the 5 leading blanks are there!

Then the string is transferred to the Excel worksheet, but the leading blanks are gone.

I want to know how I can use the choices that you find under the first 2 tabs under the Excel menu Format ---> Cells.

With put '[alignment(?,?,?,?,?)]'; I think the second tab's alternatives might be used. But I don't know how get what I want. I have read the macrofun.hlp file.

The first tab I don't have any idea about how to reach.

The formatting should be done from SAS and not manually after the strings are written to Excel.

I would be very grateful if you could help me.

Best regards
Susan

[deleted_user]

The example below shows the $char format that preserves leading blanks. Perhaps something you could use in your solution.

data class;
length new_name $13;
format new_name $char13.;
set sashelp.class;
new_name = right(name);
run;

proc print data=class;
var name new_name;
format name $quote8. new_name $quote13.;
run;

[deleted_user]

dante,

As I said in my 2:06 PM comment: The code worked; the 5 leading blanks are there, in my SAS table.

They disappear when written to the Excel worksheet.

Susan

[Cynthia_sas]

Hi:
I believe that Excel is stripping out the leading blanks, even though they're there when SAS creates the variable -- as you've verified.

This behavior also happens to people who have numbers with leading zeroes -- the leading zeroes will get stripped/ignored by Excel. People who use TAGSETS.EXCELXP can pass a custom Excel format via the TAGATTR= style attribute -- which then writes the correct XML for passing an Excel format. However, this technique doesn't help you.

Since you are using DDE, you'll have to investigate the proper DDE commands to get Excel to apply the custom format you want.

cynthia

[deleted_user]

Sorry, must have been editing while you replied.

Just a guess, can't you place quotes around the variable, then import to excel and later have them removed.

[data_null__]

Did you select the text to be acted on by ALIGNMENT. See the following example which may help.

[pre]
*** EXCEL is running with blank BOOK1 open;
data _null_;
length command $128;
link command;
put '[SELECT.SPECIAL(5)]';
put '[CLEAR(1)]';

triplet = 'excel|[Book1]sheet1!r1c1:r19c3';
file dummy2 dde filevar=triplet;
do until(eof);
set sashelp.class end=eof;
put name age sex;
end;

** Set alignment for name column;
link command;
put '[SELECT("R1C1:R19C1")]';
put '[ALIGNMENT(4,2)]';
*put '[FORMAT.FONT(,12)]';
put '[SELECT("R1C1")]';
stop;
command:
command = 'excel|system';
file dummy1 dde filevar=command;
return;
run;[/pre]

[LinusH]

Depending on how you are going to use your Excel file, wouldn't just a single quote do the job for you?

Var1 = "'bbbbb" || Var1;

/Linus