Help using Base SAS procedures

exporting/updating an excel file

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

exporting/updating an excel file

[ Edited ]

Hi,

let's say i have an excel file stored somewhere in my pc. Can i have SAS throw data into that file  I mean is it possible that SAS can update that file with new records(same fields) every time i run my code?

 

or should i import the existing file into SAS then add the new records to it and export it back as an excel?


Accepted Solutions
Solution
‎02-10-2017 03:46 PM
Super User
Posts: 7,854

Re: exporting/updating an excel file

Using libname excel or libname xlsx, you can treat the worksheet like a library. But you can't update individual cells, you have to overwrite complete sheets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎02-10-2017 03:46 PM
Super User
Posts: 7,854

Re: exporting/updating an excel file

Using libname excel or libname xlsx, you can treat the worksheet like a library. But you can't update individual cells, you have to overwrite complete sheets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Super Contributor
Posts: 444

Re: exporting/updating an excel file

Posted in reply to KurtBremser

thank you sir

Valued Guide
Posts: 505

Re: exporting/updating an excel file

/* T009750 Modifying excel in place and retaining formatting

You have even finer control with R or Python. Many
of the techniques below require a named range, which is easy to add.

* Modifying excel in place and retaining formatting

1. Modify excel in place , maintaining previous formatting
2. Update excel cells in place, maintaining previous formatting
3. Same update excel using passthru instead of libname engine, maintaining previous formatting
4. Using a SAS table to update and excel named range, maintaining formatting
5. Passthu 'insert into' an existing excel named range, add row

In a previous post I showed how
to deal with messy headers. It is
possible to input just the headers and
map them to useful SAS names.
Then you can modify inplace the excel
data, maintaining the formatting;

I have also demonstrated using MS SQL on excel data before importing into SAS.

%let fyl=d:\temp\&pgm..xlsx;


* create a table in excel;

%utlfkil(&fyl); * delete if excel file exists;
libname xls "&fyl" scan_text=no;

data xls.class;
  retain key .;
  set sashelp.class;
  key=_n_;
;run;quit;

libname xls clear;

/*
Excel named rang we will us as an example
Up to 40 obs XLS.class total obs=19

     Obs    KEY    NAME       SEX    AGE    HEIGHT    WEIGHT

       1      1    Alfred      M      14     69.0      112.5
       2      2    Alice       F      13     56.5       84.0
       3      3    Barbara     F      13     65.3       98.0
       4      4    Carol       F      14     62.8      102.5
       5      5    Henry       M      14     63.5      102.5
       6      6    James       M      12     57.3       83.0
       7      7    Jane        F      12     59.8       84.5
       8      8    Janet       F      15     62.5      112.5
       9      9    Jeffrey     M      13     62.5       84.0
      10     10    John        M      12     59.0       99.5
      11     11    Joyce       F      11     51.3       50.5
      12     12    Judy        F      14     64.3       90.0
      13     13    Louise      F      12     56.3       77.0
      14     14    Mary        F      15     66.5      112.0
      15     15    Philip      M      16     72.0      150.0
      16     16    Robert      M      12     64.8      128.0
      17     17    Ronald      M      15     67.0      133.0
      18     18    Thomas      M      11     57.5       85.0
      19     19    William     M      15     66.5      112.0
*/

* go into the excel file and do your formatting;

* we want to change weight to kilograms,
  change names that begin with J to Roger
  and maintain the formatting;

libname xls "&fyl" scan_text=no;

data xls.class;
   modify xls.class;
   weight=weight/2.2;
   if name=: 'J' then name='Roger';
run;

libname xls clear;


NOTE: There were 19 observations read from the data set XLS.class.
NOTE: The data set XLS.class has been updated.
There were 19 observations rewritten,
            0 observations added
        and 0 observations deleted.

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds


/*
Up to 40 obs from xls.class total obs=19

     Obs    KEY    NAME       SEX    AGE    HEIGHT     WEIGHT

       1      1    Alfred      M      14     69.0     51.1364
       2      2    Alice       F      13     56.5     38.1818
       3      3    Barbara     F      13     65.3     44.5455
       4      4    Carol       F      14     62.8     46.5909
       5      5    Henry       M      14     63.5     46.5909
       6      6    Roger       M      12     57.3     37.7273
       7      7    Roger       F      12     59.8     38.4091
       8      8    Roger       F      15     62.5     51.1364
       9      9    Roger       M      13     62.5     38.1818
      10     10    Roger       M      12     59.0     45.2273
      11     11    Roger       F      11     51.3     22.9545
      12     12    Roger       F      14     64.3     40.9091
      13     13    Louise      F      12     56.3     35.0000
      14     14    Mary        F      15     66.5     50.9091
      15     15    Philip      M      16     72.0     68.1818
      16     16    Robert      M      12     64.8     58.1818
      17     17    Ronald      M      15     67.0     60.4545
      18     18    Thomas      M      11     57.5     38.6364
      19     19    William     M      15     66.5     50.9091
*/

* lets update just two cells;

libname xls "&fyl" scan_text=no;
proc sql;
    update xls.class
    set age=199,
        sex='U'
    where key=2;
quit;

/*
Up to 40 obs XLS.class total obs=19

  KEY    NAME       SEX    AGE    HEIGHT     WEIGHT

    1    Alfred      M      14     69.0     51.1364
    2    Alice       U     199     56.5     38.1818
    3    Barbara     F      13     65.3     44.5455
*/

* update with passthru;

proc sql dquote=ansi;

  connect to excel (Path="d:\temp\&pgm..xlsx");

    execute(
      update `d:\temp\&pgm`.class class
      set age=888,
          weight=789
      where key=1
    ) by excel;

    disconnect from Excel;

Quit;

/*
Up to 40 obs from xls.class total obs=19

 KEY    NAME       SEX    AGE    HEIGHT     WEIGHT

   1    Alfred      M     888     69.0     789.000
   2    Alice       U     199     56.5      38.182
   3    Barbara     F      13     65.3      44.545
*/

* using a table to modify a table in excel;

libname xls "&fyl" scan_text=no;

data xls.class;
   modify xls.class;
   weight=weight/2.2;
   if name=: 'J' then name='Roger';
run;

libname xls clear;


/*
Up to 40 obs from xls.class total obs=19

 KEY    NAME       SEX    AGE    HEIGHT     WEIGHT

   1    Alfred      M     888     69.0     358.636
   2    Alice       U     199     56.5      17.355
   3    Barbara     F      13     65.3      20.248
   4    Carol       F      14     62.8      21.178
   5    Henry       M      14     63.5      21.178
*/

* create a SAS class datsset to replace the
  one in excel;

data classnew;
  retain key 0;
  set sashelp.class;
  key=_n_;
  if mod(key,3)=0 then do;
     weight=weight/2.2;
     height=height*2.54;
     age=age*2;
  end;
;run;quit;

libname xls "&fyl" scan_text=no;

data xls.class;
   modify xls.class;
   set classnew;
run;

libname xls clear;


proc sql dquote=ansi;

  connect to excel (Path="d:\temp\&pgm..xlsx");

    execute(insert into class
     values(20, 'Jane', 'F', 120, 120,120)) by excel;

    disconnect from Excel;

Quit;

/*
1045  proc sql dquote=ansi;
1046    connect to excel
1046! (Path="d:\temp\&pgm..xlsx");
SYMBOLGEN:  Macro variable PGM resolves to keepxlsfmt

1047      execute(insert into class
1048       values(20, 'Jane', 'F', 120, 120,120)) by excel;
1049      disconnect from Excel;
1050  Quit;
NOTE: PROCEDURE SQL used (Total process time):
*/

/*
Added a 20th observation

Up to 40 obs from xls.class total obs=20

  KEY    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
    6    James       M      12      57.3      83.0
    7    Jane        F      12      59.8      84.5
    8    Janet       F      15      62.5     112.5
    9    Jeffrey     M      13      62.5      84.0
   10    John        M      12      59.0      99.5
   11    Joyce       F      11      51.3      50.5
   12    Judy        F      14      64.3      90.0
   13    Louise      F      12      56.3      77.0
   14    Mary        F      15      66.5     112.0
   15    Philip      M      16      72.0     150.0
   16    Robert      M      12      64.8     128.0
   17    Ronald      M      15      67.0     133.0
   18    Thomas      M      11      57.5      85.0
   19    William     M      15      66.5     112.0
   20    Jane        F     120     120.0     120.0
 */
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 200 views
  • 1 like
  • 3 in conversation