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
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.
Usually the NOTAB option along with a LRECL setting avoids this issue.
Can you try setting LRECL to 1000 rather than MAX?
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.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.