Help using Base SAS procedures

Out put characters to Excel using DDE

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Out put characters to Excel using DDE

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;

 

 


Accepted Solutions
Solution
‎03-21-2016 07:43 PM
Trusted Advisor
Posts: 1,117

Re: Out put characters to Excel using DDE

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Out put characters to Excel using DDE

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#n1aqiv6biqkjbnn1gu...

 

Super User
Super User
Posts: 7,942

Re: Out put characters to Excel using DDE

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.

Super User
Posts: 5,424

Re: Out put characters to Excel using DDE

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.

Data never sleeps
Contributor
Posts: 67

Re: Out put characters to Excel using DDE

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.
Super User
Posts: 19,770

Re: Out put characters to Excel using DDE

[ Edited ]

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;

 

 

 

Contributor
Posts: 67

Re: Out put characters to Excel using DDE

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.

 

Contributor
Posts: 67

Re: Out put characters to Excel using DDE

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
Super User
Super User
Posts: 7,942

Re: Out put characters to Excel using DDE

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.  

Contributor
Posts: 67

Re: Out put characters to Excel using DDE

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
Contributor
Posts: 67

Re: Out put characters to Excel using DDE

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.
Super User
Posts: 11,343

Re: Out put characters to Excel using DDE

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.

Solution
‎03-21-2016 07:43 PM
Trusted Advisor
Posts: 1,117

Re: Out put characters to Excel using DDE

[ Edited ]

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;

Contributor
Posts: 67

Re: Out put characters to Excel using DDE

Posted in reply to FreelanceReinhard

Hi FreelanceReinhard,

 

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

 

Really appreciated your help.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 722 views
  • 5 likes
  • 6 in conversation