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

saskapa — Tue, 16 Feb 2016 20:02:35 GMT

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

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

Reeza — Tue, 16 Feb 2016 20:05:00 GMT

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

Can you try setting LRECL to 1000 rather than MAX?

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

ballardw — Tue, 16 Feb 2016 20:06:46 GMT

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.

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

saskapa — Wed, 17 Feb 2016 15:51:03 GMT

Hi ballardw, Taking care of CR and LF solved the issue ! Thannks saskapa

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

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

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

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

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