Help using Base SAS procedures

How to update Excel spreadsheet from SAS

New Contributor NL
New Contributor
Posts: 4

How to update Excel spreadsheet from SAS

I have an Excel spreadsheet that has lots attributes/formats such as bolded fonts, color fonts, highlighted cells & etc and I need to keep them. There is one particular column that I need to update/modify based on the SAS dataset. See example below. My code is working fine in a 32 bit system (PC SAS 9.2) and noticed that it doesn't work in a 64 bit system (also in PC SAS 9.2). The MS Office is in 32 bit. I have installed PC Files Server so reading in the Excel data is not an issue. The issue is when I was trying to replace a particular column in Excel. Based on SAS usage note 42151 (Usage Note 42151: PCFILES engine does not support MODIFY in a DATA Step). I'm wondering if there is another option as I don't want to export the entire SAS dataset to Excel and lose all the pre-existing attributes/format as stated above. Any pointer is greatly appreciated.

Table 1 is my master table:
1. to be read into SAS
2. this table is to be updated in my code
3. primary key is variable ID
4. the variable (or column) to be updated is the variable Programmed
Table 2 is another table
1. to be read into SAS
2. primary key is var ID
Table 1 and Table 2 will be merged in SAS by ID (a new table in SAS say table ALL is created), the var Programmed (value in table 2) will overwrite var Programmed (value in table 1) in table ALL.
My original code modifies Table 1 based on table ALL.
Here is the code for this example
*assuming table 1 and table 2 are individual Excel file with data in sheet1 in each file;
libname t1 pcfiles path = "c:\users\table1.xls" scan_textsize=yes dbmax_text=32767;
libname t2 pcfiles path = "c:\users\table2.xls" scan_textsize=yes dbmax_text=32767;
libname myexl pcfiles path = "c:\users\table1.xls" SCAN_TEXTSIZE=YES dbMax_text=32767;

Data table1;
set t1.'sheet1$'n(drop=Programmed);

data table2;
set t2.'sheet1$'n;

proc sort data=table1; by id; run;
proc sort data=table2; by id; run;

data ALL;
merge t1(in=in1) t2(in=in2);
by id;
if in1 and in2;
*here is where I get the SAS ERROR as SAS can't "modify" table1.xls;
data myexl.'sheet1$'n;
modify myexl.'sheet1'n ALL;
by id;

libname t1 clear;
libname t2 clear;
libname myexl clear;
Ask a Question
Discussion stats
  • 0 replies
  • 1 in conversation