DATA Step, Macro, Functions and more

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

Reply
Regular Contributor
Posts: 192

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

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!

Super User
Posts: 17,785

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

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

Regular Contributor
Posts: 192

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

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)

Super User
Posts: 17,785

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

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

Regular Contributor
Posts: 192

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

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

Super User
Posts: 17,785

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

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

Regular Contributor
Posts: 192

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

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

Super User
Posts: 17,785

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

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?

Super User
Posts: 10,487

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

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

Valued Guide
Posts: 2,174

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

replace

set acc.Table90-Table13: indsname=source;


with


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

good luck

Ask a Question
Discussion stats
  • 9 replies
  • 295 views
  • 0 likes
  • 4 in conversation