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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
Reeza
Super User

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

 

Can you try setting LRECL to 1000 rather than MAX?

ballardw
Super User

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.

saskapa
Quartz | Level 8
Hi ballardw, Taking care of CR and LF solved the issue ! Thannks saskapa

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!

How to Concatenate Values

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.

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
  • 3 replies
  • 869 views
  • 0 likes
  • 3 in conversation