Get multiple data sets from Access, compute, and export to xls

[jcis7]

I've about 15 data sets I need to obtain the same fields from (code, name, total, num_cats)

dataset names: table1, table2, table3, table4

dataset 1:

code      name       total        num_cats

12345    Facility1    10          2

12459   Facility2      20         20

etc..  (each code is unique in dataset 1)

new dataset from dataset 1:

code      name       total        num_cats  pct_cats

12345    Facility1    100         2               2

12459   Facility2      20         20            100

How do I get each table with the fields (i.e. in dataset 1) from ms access efficiently into sas?

How do I perform the same calculation (i.e. get percent_cats) from each data table?

And, lastly, how do I export each new dataset into separate excel worksheets in one excel workbook?

Any help you can give is appreciated!

[Reeza]

1. Connect to your access book via libname acc access 'path to access file';

2. You can then refer to datasets all at once, and using colon modifier (as below)

data want;

set acc.table:  indsname=source;

pct_cats=num_cats/total*100;

file_name=source;

run;

3. I can't follow the calculation. You say code is unique, but the calc shows a different total below. If its just a typo and straight division you can do it in the data step above.

4. See tagsets.excelxp to export to different each table out using the by variable file_name from above.

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

[jcis7]

Sorry,  code 12345 is supposed to have total = 100.

Thanks!

dataset 1:

code      name       total        num_cats

12345    Facility1    100          2

12459   Facility2      20         20

etc..  (each code is unique in dataset 1)

[Reeza]

Then it seems like the two steps I suggested above would work

[jcis7]

For the inds statement, what if there're other tables in the database that may start with 'Table..'?

The tables I want are called 'Table99', 'Table00', 'Table 01', etc,,

[Reeza]

Then you need to specify them either explicitly or using Table01-Table99. Depends on your naming convention and what the other table names are.

[jcis7]

The following doesn't work; the naming convention it table and year:

set acc.Table90-Table13: indsname=source;

For example, table names I want to import are:

table90

table91

table92

table93

table94

table95

table96

table97

table98

table99

table00

table01

.....

table12

[Reeza]

hmm...what are the table names that you don't want to import? Are you only doing this once or setting up a repeated process?

[ballardw]

jcis7 wrote:


For example, table names I want to import are:
table90
table91
table92
table93
table94
table95
table96
table97
table98
table99
table00
table01
.....
table12

Another example of Y2K problems still floating around...

[Peter_C]

replace

set acc.Table90-Table13: indsname=source;

with

set acc.Table90-acc.Table99 acc.Table00-acc.Table13 indsname=source;

good luck