topic Re: Out put characters to Excel using DDE in SAS Procedures

Out put characters to Excel using DDE

Belle — Wed, 16 Mar 2016 23:03:01 GMT

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;

Re: Out put characters to Excel using DDE

Reeza — Thu, 17 Mar 2016 01:42:04 GMT

In front of all your variables? Remove the dlm from your code, Excel and SAS will handle putting the values into the correct cells.

http://support.sas.com/documentation/cdl/en/hostwin/63047/HTML/default/viewer.htm#n1aqiv6biqkjbnn1gu1388hp7aab.htm

Re: Out put characters to Excel using DDE

RW9 — Thu, 17 Mar 2016 09:27:34 GMT

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.

Re: Out put characters to Excel using DDE

LinusH — Thu, 17 Mar 2016 12:29:10 GMT

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.

Re: Out put characters to Excel using DDE

Belle — Thu, 17 Mar 2016 15:14:09 GMT

Hi Reeza,

I cant remove DLM because I have a blank between 'Bay' and 'Area'. If I remove DLM, column A will be Bay and column B will be Area, which is not what I want.

Thanks for the suggestion.

Re: Out put characters to Excel using DDE

Reeza — Thu, 17 Mar 2016 15:20:12 GMT

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;

Re: Out put characters to Excel using DDE

Belle — Thu, 17 Mar 2016 15:20:26 GMT

Hi Raw,

The example here is very simple, my real report is more complicated which request data from different sources, and need to be input to certain cells in Excel. I found DDE is very useful in my situation. Instead of copy and paste to certain cells, I can use DDE to automate the process.

It would be a big lost for people like me if DDE won't be supported. I hope NOT.

Thanks

Re: Out put characters to Excel using DDE

Belle — Thu, 17 Mar 2016 15:30:45 GMT

Thanks Reeza for getting back to me. I did remove DLM before, but it doesnt work for me. I noticed I have no problem if output is numerical data, but doesn't work for characters.

I won't be able to access my SAS in next couple days, but I will try again once I have the access. I will let you know.

Thanks again

Re: Out put characters to Excel using DDE

RW9 — Thu, 17 Mar 2016 15:31:25 GMT

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.

Re: Out put characters to Excel using DDE

Belle — Thu, 17 Mar 2016 17:53:48 GMT

Sorry for typing your name wrong, RW9, I was typing on my phone. Thanks for your advice, I will definitely keep it in mind in the future.

Re: Out put characters to Excel using DDE

ballardw — Thu, 17 Mar 2016 22:30:58 GMT

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.

Re: Out put characters to Excel using DDE

FreelanceReinh — Fri, 18 Mar 2016 14:37:21 GMT

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;

Re: Out put characters to Excel using DDE

Belle — Mon, 21 Mar 2016 23:42:36 GMT

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.

Re: Out put characters to Excel using DDE

Belle — Mon, 21 Mar 2016 23:44:54 GMT

Hi FreelanceReinhard,

Thanks for your solution, it works. This has to do with variable and string as your mentioned.

Really appreciated your help.