/* 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
*/
Click image to register for webinar
