SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Deleting or tagging Strikethrough rows from an excel file using SAS

Reply
New User
Posts: 1

Deleting or tagging Strikethrough rows from an excel file using SAS

Hi,

 

I am trying to import an excel sheet which has few rows having values which are striked out. example CP & TRT values in column A

.Excel file.jpg

 

I am working on SAS version 9.2 on UNIX server. I tried using DDE to mark the striked out values but it throws error :

ERROR: DDE session not ready.

FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase.

 

SAS code used:

options mlogic symbolgen;

 

options noxsync noxwait xmin;

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

%let delay=5;

data _null_;

rc=sleep(&delay);

run;

filename sas2xl dde 'excel|system';

data _null_;

rc=sleep(&delay);

run;

 

%let mapwkbk=S:\DEV\TEST.xlsx;

data _null_;

file sas2xl;

rc=sleep(&delay);

put '[open("' "&mapwkbk" '")]';

run;

 

%let sheet_id=Sheet1;

%let maxrows=10;

%let maxrecl=40;

filename origfile dde "excel|&sheet_id!r1c1:r&maxrows.c2" notab;

data original;

length col_a col_b $&maxrecl;

infile origfile dsd dlm='09'x missover pad lrecl=&maxrecl;

input col_a $ col_b $;

run;

filename origfile clear;

data _null_;

file sas2xl;

put '[workbook.next()]';

put '[workbook.insert(3)]';

run;

filename xlmacro dde "excel|macro1!r1c1:r99.c1" notab lrecl=&maxrecl;

 

%let attrcode=23;

%let marked=’<XOUT>’;

data _null_;

file xlmacro;

put '=set.name("Tag",!$b$1)';

put '=formula("' "&marked"' ",Tag)';

put '=set.name("OldValue",!$c$1)';

put '=set.name("NewValue",!$b$2)';

put '=for.cell("CurrentCell",' "'" "&sheet_id" "'"

"!$a$1:$a$&maxrows,true)";

put "=if(get.cell(&attrcode,CurrentCell),

formula(get.cell(5,CurrentCell),OldValue),)";

put '=formula("=concatenate(Tag,OldValue)",NewValue)';

put "=if(get.cell(&attrcode,CurrentCell),

formula(NewValue,CurrentCell),)";

put '=next()';

put '=halt(true)';

put '!dde_flush';

file sas2xl;

put '[run("macro1!r1c1")]';

run;

filename xlmacro clear;

 

Is there any alternative method to mark or delete these Strikedthrough values from an excel sheet using SAS code?

 

Regards

Post a Question
Discussion Stats
  • 0 replies
  • 178 views
  • 0 likes
  • 1 in conversation