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!
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.
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)
Then it seems like the two steps I suggested above would work
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,,
Then you need to specify them either explicitly or using Table01-Table99. Depends on your naming convention and what the other table names are.
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
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?
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...
replace
set acc.Table90-Table13: indsname=source;
with
set acc.Table90-acc.Table99 acc.Table00-acc.Table13 indsname=source;
good luck
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.