topic How do you update named range with xlsx engin in SAS Programming

How do you update named range with xlsx engin

gyambqt — Thu, 23 Jun 2016 00:01:24 GMT

Hi Experts,

Is there any way to update the named range within excel using xlsx in EG?

I have a client whose EG is running on linux server with no PCfile server installed. I want to export the data into a pre-defined Excel template without using proc export (you can't specify cell range) or ODS as I need to load the data to the proper cell ranges (defined by named range). I need to maintain the excel format while update the data in the named range. Is it possible to achieve that? (Client does not have AMO)

Re: How do you update named range with xlsx engin

Reeza — Thu, 23 Jun 2016 01:08:49 GMT

What version of SAS? (not EG).

I think the latest version allows it, but not earlier than 9.4.

Re: How do you update named range with xlsx engin

gyambqt — Thu, 23 Jun 2016 01:12:27 GMT

client is using SAS9.4 M2 with SAS/Access installed and licensed

Re: How do you update named range with xlsx engin

Reeza — Thu, 23 Jun 2016 01:58:04 GMT

Named ranges are not supported.

http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Re: How do you update named range with xlsx engin

rogerjdeangelis — Thu, 23 Jun 2016 20:26:47 GMT

Excel: Replacing data in a named range while maintaing
formatting without SAS Access to PC Files

This post calls R to replace data in the named range.
You may need full SAS for this, especially if you are locked
out of a an operating system.

HAVE

* create excel file with named range "class";
* I am doing this to provide a excel file with a named range;
* you already have the a excel sheet with named range;

* create named range "class";
%utlfkil(d:\xls\class.xlsx); * delete if exists;
libname xls "d:\xls\class.xlsx";
data xls.class;
  set sashelp.class;
;run;quit;

proc contents data=xls._all_;
;run;quit;

libname xls clear;

/*
Libref         XLS
Engine         EXCEL
Physical Name  d:\xls\class.xlsx
User           Admin


                                              DBMS
           Member  Obs, Entries               Member
#  Name    Type     or Indexes   Vars  Label  Type

1  class   DATA         .         5           TABLE
2  class$  DATA         .         5           TABLE


to 40 obs from d:\xls\class.xlsx total obs=19

Rows 1-6 are highlighted in yellow

  Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

    1    Alfred      M      14     69.0      112.5
    2    Alice       F      13     56.5       84.0
    3    Barbara     F      13     65.3       98.0
    4    Carol       F      14     62.8      102.5
    5    Henry       M      14     63.5      102.5
....
*/

WANT (with rows 1-6 highlighted in yellow - retained)
(sorted by descending name)

        NAME SEX AGE HEIGHT WEIGHT
  1   William  M  15   66.5  112.0
  2   Thomas   M  11   57.5   85.0
  3   Ronald   M  15   67.0  133.0
  4   Robert   M  12   64.8  128.0
  5   Philip   M  16   72.0  150.0

SOLUTION

* Create some replacement data;

* dataset to overwrite named region data keeping your customizations;
proc sort data=sashelp.class out="d:/sd1/newclass.sas7bdat";
by descending name;
run;quit;

* overwite data in named range class;
%utl_submit_r64(%nrbquote(
   library(XLConnect);
   library(haven);
   newclass<-read_sas("d:/sd1/newclass.sas7bdat");
   wb <- loadWorkbook("d:/xls/class.xlsx");
   /* set Style says to retain formatting */
   setStyleAction(wb,XLC$"STYLE_ACTION.NONE");
   writeNamedRegion(wb,newclass,name="class");
   saveWorkbook(wb);
  ));


%macro utl_submit_R64(pgmx)/des="Semi colon separated set of R commands";
  * write the program to a temporary file;
  filename r_pgm temp lrecl=32766 recfm=v;
  data _null_;
    file r_pgm;
    pgm=compbl("&pgmx");
    put pgm;
    putlog pgm;
  run;
  %let __loc=%sysfunc(pathname(r_pgm));
  * pipe file through R;
  filename rut pipe "c:\Progra~1\R\R-3.2.4\bin\x64\R --quiet --no-save < &__loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
    putlog _infile_;
  run;
  filename rut clear;
  filename r_pgm clear;
%mend utl_submit_r64;

Re: How do you update named range with xlsx engin

Reeza — Fri, 24 Jun 2016 17:36:24 GMT

You can try the macro here. Not sure if it works on Unix. http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export