topic Re: Create Excel with Links to another Sheet in ODS and Base Reporting

Create Excel with Links to another Sheet

Patrick — Sat, 21 May 2022 08:37:53 GMT

Hi all,

I'm trying to generate an Excel workbook that has a summary page with links to other sheets.

This is part of a DQ process that creates an Excel which then gets attached to a decision node in a workflow for users to decide if the process can proceed or if there is a need to first fix the data and re-run.

What I'm trying to generate is a link like one could create manually in Excel.

Is this possible using SAS? And if yes can you please point me to the right place in the docu or even better some white paper with a sample script. I couldn't find anything so far.

I'm almost certain I could make this work using Python but I'd like to use SAS if possible.

Thanks,

Patrick

Re: Create Excel with Links to another Sheet

Ksharp — Sat, 21 May 2022 09:57:58 GMT

Yes. You can do. Here is an example. All you need to do is make a URL style like:

call define(_col_,'url',cats('#''',Sheet1,'''!A1') );

Here "Sheet1" is Sheet Name(put # before it). "A1" is the cell you want to point inside Sheet1.

Also I pack my code as a attachment, you can run it to know how it work.

Good Luck!

%let path=%sysfunc(prxchange(s/[^\\]+$//,1,%sysget(SAS_EXECFILEPATH)));
options validvarname=any validmemname=extend;
%put &path ;
/***导入需要保留的 受试者**/
proc import datafile="&path.\01批datalisting106名受试者.xlsx" out=_have_ dbms=xlsx replace;
run;

/*原始excel*/
*libname x xlsx "&path.\ODM_Datasets_XLS_Site-All_zh-CN_20210113114550(1).xlsx";
libname x excel "&path.\ODM_Datasets_XLS_Site-All_zh-CN_20210113114550(1).xlsx";

proc sql noprint;
create table _dsn_ as
select nliteral(memname) as memname,sum(upcase(name)='SUBJECT_ID') as flag 
  from dictionary.columns
   where libname='X'
    group by memname
     order by flag,input(compress(memname,,'kd'),best.);
quit;
data _null_;
 set _dsn_ end=last;
 if _n_=1 then call execute('proc sql;');
 if flag then call execute(cat('create table _',compress(memname,,'kad'),' as select * from x.',memname,
              ' where subject_id="筛选号" or subject_id in (select subject_id from _have_);'));
	else call execute(cat('create table _',compress(memname,,'kad'),' as select * from x.',memname,';'));
 if last then call execute('quit;');
run;


/*Copy 数据到 另一个excel中*/
ods _all_ close;
data _null_;
 set _dsn_ end=last;
 if _n_=1 then call execute('ods excel file="&path.\需要保留的受试者数据.xlsx" ;');
 if find(memname,'eCRF表单','i') then 
    call execute(catt("ods excel options(sheet_name='",scan(memname,1,'"''$& ',),"') ;
                       proc report data=_",compress(memname,,'kad')," nowd;
                       compute 表单名称;
 call define(_col_,'url',cats('#''',表单代码,'''!A1') );
 call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
                       run;"  ));
else do;
 call execute(catt("ods excel options(sheet_name='",scan(memname,1,'"''$',),"') ;"));
 call execute(catt('proc report data=_',compress(memname,,'kad'),' nowd;run;' ));
end;
 if last then call execute('ods excel close;');
run;

Re: Create Excel with Links to another Sheet

Ksharp — Sat, 21 May 2022 10:02:29 GMT

Here is an example :

call define(_col_,'url',' #Sheet2!A1 ' );

It stands for this cell could link to A1 of Sheet2 .

Re: Create Excel with Links to another Sheet

Ksharp — Sat, 21 May 2022 10:04:02 GMT

call define(_col_,'style','style={textdecoration=underline color=blue}');

This could make the cell like have a link .

Re: Create Excel with Links to another Sheet

Ksharp — Sat, 21 May 2022 10:10:40 GMT

ods excel file='c:\temp\want.xlsx' options(sheet_name='class');
proc report data=sashelp.class nowd;
compute name;
n+1;
if n in (2 8 12) then do;
call define(_col_,'url',' #car!A1 ' );
call define(_col_,'style','style={textdecoration=underline color=blue}');
end;
endcomp;
run;
ods excel  options(sheet_name='car');
proc report data=sashelp.heart(obs=20) nowd;
columns status weight height bp_status;
compute status;
n+1;
if n in (2 8 12) then do;
call define(_col_,'url',' #class!A1 ' );
call define(_col_,'style','style={textdecoration=underline color=blue}');
end;
endcomp;
run;
ods excel close;

Whatever. Here is an example .

Re: Create Excel with Links to another Sheet

Patrick — Sun, 22 May 2022 02:13:58 GMT

That does the trick. Thank you! 🙂

I've been very close (based on an answer you've given for an earlier question) but missed how to correctly reference another sheet within the same Excel workbook.