Hello,
I have a dataset that most of the fields are characters. When I output this dataset to Excel using DDE, there is a space before each characters. How can I get ride of the space?
Thanks in advance!
Here is my program:
Options noxwait noxsync;
X "'&TemplatePath.&TemplateName..xlsx'";
Options xwait xsync;
FILENAME ddecmd DDE 'EXCEL|SYSTEM';
FILENAME DATA DDE "EXCEL|sheet1!R2C1:R7C2" NOTAB;
DATA _null_;
SET file;
FILE DATA LRECL=30000;
DLM='09'X;
PUT Region DLM Dept DLM;
run;
DATA _NULL_;
FILE ddeCMD;
PUT "[SAVE.AS(%BQUOTE("&SavePath.&SaveName._&Sysdate..xlsx "))]";
PUT '[CLOSE()]' ;
Replace;
RUN;
Hello @Belle,
I think you shouldn't create a variable DLM, but type the '09'x directly into the PUT statement. I can't test it at the moment with Excel because I don't have Excel installed on my SAS workstation, but this is how I wrote my DDE programs in the past and it makes a difference if I write to a text file.
put region '09'x dept;
(No '09'x is necessary after the last variable, I think.)
To quote the documentation of PUT Statement, List:
"When a variable is written with list output, SAS automatically inserts a blank space. (...) However, when a character string is written, SAS does not automatically insert a blank space."
Edit: For the purpose of abbreviation you could define a macro variable: %let t='09'x;
In front of all your variables? Remove the dlm from your code, Excel and SAS will handle putting the values into the correct cells.
Please don't type code in all upper case it is very hard to read. Can I ask why you are using DDE? It would not be my recommendation, it is a very old technology which doesn't work in some scenarios, and may not be suppoorted at all soon. If you absolutely have to insert data into a template spreadsheet, then a simpler method is to export the data as CSV, and have your spreadsheet load that data on open, or have a separate macro file in Excel which loads the CSV and saves to the Excel file. Or read in the document and re-create it with the updates. Or if you have SAS9.4 use Libname xlsx to manipulate the file. Or don't use Excel at all which is Always the best idea.
Chime in with @RW9.
Another option that really lets you design your Excel spreadsheet is to use the Add-in for MS Office. Available in the BI Server and Office Analytics offerings.
The NOTAB with a LRECL on your DDE statement should take care of that. At least it does for me.
FILENAME DATA DDE "EXCEL|sheet1!R2C1:R7C2" NOTAB LRECL=30000;
DATA _null_;
SET file;
FILE DATA;
PUT Region Dept ;
run;
Hi Reeza,
I tried your method, it ends at all the fields are in one column. I also tried FreelanceReinhard method, it works.
Thanks again for the advice.
By RAW I assume you mean me. DDE is very old technology, it was invented back in the early days of Office, and is controlled by Microsoft, so support is down to them. It has very limited functionality, and doesn't work at all in some scenarios, hence I would again suggest not using it. There are many ways to get data to a from Excel - noting of course that Excel is no the best file format for any purpose in the first place - so there are many options. Why, for instance, do you need to access certain cells, what if someone inserts a line, or changes a name or any other of the million differences that using Excel can have on your code.
This line of code tells me you have two variables of interest, Regiod and Dept
PUT Region DLM Dept DLM;
You comments say that you want the values of those in a single cell.
I would propose a generic solution of concatenating them in a data step so the values are in a single variable and that is what is exported.
RegDept = catx(' ',Region,Dept);
to concatenate with a space between the values.
Hello @Belle,
I think you shouldn't create a variable DLM, but type the '09'x directly into the PUT statement. I can't test it at the moment with Excel because I don't have Excel installed on my SAS workstation, but this is how I wrote my DDE programs in the past and it makes a difference if I write to a text file.
put region '09'x dept;
(No '09'x is necessary after the last variable, I think.)
To quote the documentation of PUT Statement, List:
"When a variable is written with list output, SAS automatically inserts a blank space. (...) However, when a character string is written, SAS does not automatically insert a blank space."
Edit: For the purpose of abbreviation you could define a macro variable: %let t='09'x;
Hi FreelanceReinhard,
Thanks for your solution, it works. This has to do with variable and string as your mentioned.
Really appreciated your help.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.