DATA Step, Macro, Functions and more

Export SAS dataset with DDE: Broken lines in my xlsx output file

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Export SAS dataset with DDE: Broken lines in my xlsx output file

[ Edited ]

Hi,

 

 

I have an issue with export a SAS dataset to excell by using DDE. That SAS dataset was originally import with from an excel file by using DDE as well. 

 

That excel file is a sepcification document with a column called 'Comment/Programming'. This column has contains a lot strings...

When I imported  this xlsx file in SAS everything went file here is a section of the import :

 

 


data _null_;
 file ddecmd;
 put "[OPEN(""C:\MPE\WORKING\METADATA\DICTIONARIES\CRT\TEMPLATE\XLS\adam\metadata.xlsx"")]";
 dummy=sleep(5);
run;


filename xlin dde "excel|&domain.!R1C1:R500C11"  lrecl=30000;




********************************************************************************;
****                    PROCESSING  SAS DATASET                              ***;
********************************************************************************;


data metadata ;
 infile xlin dsd dlm='09'x missover  notab ;
 informat var1-var11 $800.;
 input var1-var11 $800.;
run;

No issue everything perfect.

 

I use DDE to export the file into excel with following code (macro):

 

%macro xCRTxls(path=,domain=,model=,col=);


options noxwait noxsync;

x '"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"'; 


filename  ddecmd DDE 'excel|system';

*give a little time for sas to access execel;
data _null_;
  dummy=sleep(2);
run;


*Then use a Excel macro command to pass Excell via SAS ;
*-------------------------------------------------------;

data _null_;
 file ddecmd;
 put "[OPEN(""&pathx.\template.xlsx"")]";
 dummy=sleep(5);
run;

/*Note that I could create in the template.xlsx those variables  It will imporve the process and speed it up !!*/

filename test dde "excel|Sheet1!R2C1:R60C&col."  notab  LRECL=MAX;

     
data _null_; 
 retain b (-1) t '09'x;
 set metaadam.&domain.;
 
 file test;

%if %upcase(&model.) eq SDTM %then %do;
 put variable t+b  label t+b len t+b type t+b ct t+b core t+b origin t+b /*notes t+b*/ programming_instruction t+b used t+b;
 %end;
%else %if %upcase(&model.) eq ADAM %then %do;
 put  Dataset t+b Variable t+b  Label t+b  Type t+b Length t+b CT t+b Origin t+b Role t+b Core t+b  Programming t+b;
%end;

 run;

data _null_;
 file ddecmd; 
 put "[Save.as(""&pathx.\&domain..xlsx"")]";
 put "[CLOSE(""&pathx.\&domain..xlsx"")]"; 
 dummy=sleep(3);
run;
 
%mend xCRTxls;

Everything worked fine except that I have values of 'programming' column ( column that contains a lot fo character..) that are splitted.Indeed a part of those values are written in the next row to the first column.

 

The SAS log says however  that the maximum record length is 616. 

 

I have added the option DROPOVER to the file statement to see if it resolve the problem ( at cost of truncation ) but nothing changes.

 

Any idea what is happening and how to avoid this issue ?

Any help would be helpful

 

Regards

 

SAS_KAP


Accepted Solutions
Solution
‎02-17-2016 10:51 AM
Super User
Posts: 10,500

Re: Export SAS dataset with DDE: Broken lines in my xlsx output file

Check the values of your Programming variable for things like Tabs, Carriage Returns or Line Feeds. They can have unexpected results on your output. If they are present, get rid of them.

View solution in original post


All Replies
Super User
Posts: 17,829

Re: Export SAS dataset with DDE: Broken lines in my xlsx output file

Usually the NOTAB option along with a LRECL setting avoids this issue.

 

Can you try setting LRECL to 1000 rather than MAX?

Solution
‎02-17-2016 10:51 AM
Super User
Posts: 10,500

Re: Export SAS dataset with DDE: Broken lines in my xlsx output file

Check the values of your Programming variable for things like Tabs, Carriage Returns or Line Feeds. They can have unexpected results on your output. If they are present, get rid of them.

Contributor
Posts: 54

Re: Export SAS dataset with DDE: Broken lines in my xlsx output file

Hi ballardw, Taking care of CR and LF solved the issue ! Thannks saskapa
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 210 views
  • 0 likes
  • 3 in conversation