import and stack vertically tables from access to sas

[jcis7]

Hi, I've >20 with the same variable names in each table which I need to import from MS Access to SAS and stack (merge?) them vertically.

Each table represents a year's worth of data.

Each table has about 10,000 observations in the following format.

The values for field one may overlap from year to year but not within the same year.

Each table is named Table70 for 1970, Table71 for 1971, through year 2012.

Table; Year 1970

field1 field2 field3 field4 field5 

001     2          3     3          2   

002     1          2     2          34

etc...

Table Year 1971

field1 field2 field3 field4 field5

001     2          4     9          9

002     1          4     9          44

etc...

Table Year 1972...etc..through

Table Year 2012

FINAL TABLE:

year  field1 field2 field3 field4 field5

1970  001     2       3        3        2

1970  002     1       2        2        34

etc...(remaining observations in Table 1970)

1971  001     2      4         9       9

1971  002      1     4        9        44

etc.. (remaining observations in Table 1971)

THROUGH Year 2012....

proc import out= work.table.&yr

     datatable="table.&yr"

     dbms = access replace;

     database = "C:\My Docs\Tables 1970-2000.mdb";

     scanmemo=yes;

     usedate=no;

     scantime=yes;

run;

How do do this using a macro statement ro iimport the tables, add a year field and stack them vertically?

I only know how to import and add a macro variable for the year:

Your help is much appreciated!

Thank you!

[PGStats · Accepted solution]

You should be able to do this as follows :

libname AC Access "C:\My Docs\Tables 1970-2000.mdb" access=readonly;

proc sql;
create table Final as
select 1970 as year, field1, field2, field3, field4, field5 from AC.Table70
union all
select 1971, field1, field2, field3, field4, field5 from AC.Table71
union all
select 1972, field1, field2, field3, field4, field5 from AC.Table72
union all
... and so on ...
union all
select 2012, field1, field2, field3, field4, field5 from AC.Table12;
quit;

PG

[jcis7]

That is so clean and beautiful;

How splendid!  Many Thanks!

What if I wanted to add another table of an earlier year which had 2 fields the tables in 1972-2012 didn't have (fieldA, fieldB)?

Table1969

field1 field2 fieldA fieldB field5

33      1          2          3     88

etc...

So the final table would be:

year field1 field2 fiieldA fieldB  field3 field4 field5

1969  001    1          2          3                           88

etc...(remaining observations in Table 1969)

1970  001     2                                3        3        2

1970  002     1                                2        2        34

etc...(remaining observations in Table 1970)

1971  001     2                               4         9       9

1971  002      1                              4        9        44

etc.. (remaining observations in Table 1971)

THROUGH Year 2012....

Any help you can give will be much appreciated!  Thanks so much!

[PGStats]

The only requirement is that the variable lists all match by position (not necessarily by name), so, assuming fieldA and fieldB are numeric, you could write :

libname AC Access "C:\My Docs\Tables 1970-2000.mdb" access=readonly;

proc sql;
create table Final as
select 1968 as year, field1, field2, field3, field4, field5, fieldA, fieldB from AC.Table68
union all
select 1969, field1, field2, field3, field4, field5, fieldA, fieldB from AC.Table69
union all
select 1970, field1, field2, field3, field4, field5, ., . from AC.Table70
union all
select 1971, field1, field2, field3, field4, field5, ., . from AC.Table71
union all
select 1972, field1, field2, field3, field4, field5, ., . from AC.Table72
union all
... and so on ...
union all
select 2012, field1, field2, field3, field4, field5, ., . from AC.Table12;
quit;

The variable names in the Final table are the ones mentioned in the first table of the union.

PG

[jcis7]

Appreciate your help so very much - Thank you!

[Ksharp]

Why not use INDSNAME option:

data x;

length dsn $ 40;

set AC.Table1970 - AC.Table2012 .....   indsname=dsn;

run;

Message was edited by: xia keshan

[PGStats]

Good idea! Glad to see you back Ksharp! - PG

[Haikuo]

Hooray for the resurrection of Ksharp!

Haikuo

[Ksharp]

Hi, guys. I miss you too.

I hope I can always stay here except for the annoying living problem.

Ksharp