BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

8 REPLIES 8
PGStats
Opal | Level 21

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

PG
jcis7
Pyrite | Level 9

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
Opal | Level 21

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

PG
jcis7
Pyrite | Level 9

Appreciate your help so very much - Thank you!

Ksharp
Super User

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
Opal | Level 21

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

PG
Haikuo
Onyx | Level 15

Hooray for the resurrection of Ksharp!

Haikuo

Ksharp
Super User

Hi, guys. I miss you too.

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

Ksharp

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 8 replies
  • 1144 views
  • 2 likes
  • 4 in conversation