topic Re: import and stack vertically tables from access to sas in SAS Programming

import and stack vertically tables from access to sas

jcis7 — Fri, 12 Apr 2013 22:57:32 GMT

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!

Re: import and stack vertically tables from access to sas

PGStats — Sat, 13 Apr 2013 00:03:09 GMT

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;

Re: import and stack vertically tables from access to sas

jcis7 — Sat, 13 Apr 2013 00:51:35 GMT

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!

Re: import and stack vertically tables from access to sas

PGStats — Sat, 13 Apr 2013 01:32:37 GMT

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.

Re: import and stack vertically tables from access to sas

jcis7 — Thu, 18 Apr 2013 15:24:04 GMT

Appreciate your help so very much - Thank you!

Re: import and stack vertically tables from access to sas

Ksharp — Sat, 20 Apr 2013 09:23:50 GMT

Why not use INDSNAME option:

data x;

length dsn $ 40;

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

run;

Message was edited by: xia keshan

Re: import and stack vertically tables from access to sas

PGStats — Sat, 20 Apr 2013 12:09:47 GMT

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

Re: import and stack vertically tables from access to sas

Haikuo — Sat, 20 Apr 2013 13:04:34 GMT

Hooray for the resurrection of Ksharp!

Haikuo

Re: import and stack vertically tables from access to sas

Ksharp — Sun, 21 Apr 2013 06:52:20 GMT

Hi, guys. I miss you too.

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

Ksharp