BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

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!

9 REPLIES 9
Reeza
Super User

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
Pyrite | Level 9

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
Super User

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

jcis7
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Super User

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
Super User

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
Rhodochrosite | Level 12

replace

set acc.Table90-Table13: indsname=source;


with


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

good luck

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1069 views
  • 0 likes
  • 4 in conversation