1

I need to update a table with past missing information using past versions of the same table. The update is needed as some earlier information are not available anymore in the most recent table. Let tableA be the table at time0 and tableB the table at time1 and so on. What i'm really interested in is the last updated table.

So far i have tried this method;

create view _tableB
select * 
from tableA 
union 
select * 
from tableB a 
where a.id not in (select id from tableA);

then I proceeded with:

create view _tableC
select *
from _tableB
union 
select *
from tableC a
where a.id not in (select id from _tableB);

and so on till i reach my final table where i create a table. 

create table _tableT 
select * 
from _tableS 
union 
select * 
from tableT a
where a.id not in (select id from _tableS);

Do you see something better up here?

P.S: I have to mention that for each observations i can have many languages. The information was in this way

id|lguage1|lguage2|lguage3|

and i put it wide2long using a view and this method.

id1|lguage1 
id1|lguage2 
id1|lguage3
id2|lguage1

The informations are not sorted by id and language.
Thanks.

Community
  • 1
  • 1
DJJ
  • 2,481
  • 2
  • 28
  • 53
  • And do you need to put it first into a more vertical structure? Or can you leave it flat up to this point (so all languages in single record) and do the 'verticalisation' (1 record per id, per language)? – mvherweg Aug 27 '13 at 15:31
  • I thought that the verticalisation would facilitate the update for different language as far as the coding is concerned. But I'm not sure if it is more time consuming or not. However i've noticed that the view is really time consuming when outputing the final table. So i abondoned and replaced 'view' by 'table' in the code. – DJJ Aug 28 '13 at 15:48

1 Answers1

1

Given a couple of assumptions, you can simplify this greatly:
1. Each individual dataset has no duplicate ID's.
2. Each dataset is sorted by ID.

Then you simply do this: data RESULT; set _tableS ... _tableC _tableB _table; by id; if first.id; run;

This way, the code is more compact and all data is only read once. It will read in all the tables and output an interweaved result (i.e.: the result will also by sorted on ID)

The firs.id filter means that he will only keep the first record of each id value he encounters. Since the datasets are mentioned with the most recent first, he will take the most recent record available for each ID and ditch the others.

mvherweg
  • 1,272
  • 9
  • 11
  • The method is definetly interesting. But My problem is a bit more complicated than that. I have updated my question. – DJJ Aug 27 '13 at 07:26