topic Re: Get multiple data sets from Access, compute, and export to xls in SAS Programming

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

jcis7 — Wed, 04 Sep 2013 21:59:34 GMT

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!

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

Reeza — Wed, 04 Sep 2013 23:17:36 GMT

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

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

jcis7 — Wed, 04 Sep 2013 23:23:20 GMT

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)

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

Reeza — Wed, 04 Sep 2013 23:25:26 GMT

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

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

jcis7 — Wed, 04 Sep 2013 23:39:09 GMT

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,,

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

Reeza — Wed, 04 Sep 2013 23:40:38 GMT

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

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

jcis7 — Thu, 05 Sep 2013 00:03:02 GMT

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

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

Reeza — Thu, 05 Sep 2013 00:12:13 GMT

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?

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

ballardw — Thu, 05 Sep 2013 15:08:04 GMT

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...

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

Peter_C — Sat, 07 Sep 2013 11:13:48 GMT

replace

set acc.Table90-Table13: indsname=source;

with

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

good luck