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.
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.
Did you select the text to be acted on by ALIGNMENT. See the following example which may help.
*** EXCEL is running with blank BOOK1 open;
length command $128;
triplet = 'excel|[Book1]sheet1!r1c1:r19c3';
file dummy2 dde filevar=triplet;
set sashelp.class end=eof;
put name age sex;
** Set alignment for name column;
command = 'excel|system';
file dummy1 dde filevar=command;