BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Belle
Obsidian | Level 7

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

13 REPLIES 13
Reeza
Super User

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

LinusH
Tourmaline | Level 20

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
Belle
Obsidian | Level 7
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.
Reeza
Super User

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;

 

 

 

Belle
Obsidian | Level 7

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.

 

Belle
Obsidian | Level 7
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Belle
Obsidian | Level 7
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
Belle
Obsidian | Level 7
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.
ballardw
Super User

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.

FreelanceReinh
Jade | Level 19

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;

Belle
Obsidian | Level 7

Hi FreelanceReinhard,

 

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

 

Really appreciated your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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