Jessica Warren Jessica Warren - 1 month ago 5
SQL Question

Append new table statistics to existing table next row with existing identifier

I have multiple tables all carrying information on the statistics for different zipcodes in a state. In order to do this ran a loop that went through them all so I had (zipcode1 zipcode2 zipcode2) This worked well, but now I am trying to create a master table of all the stats for each table in one appended table. How do I say

proc sql;
create table wanted_all_zips
select stats
from (

tables that are being overwritten each time or I can
append the zipcode to each table so they don't get
overwritten but I do not know how to append each
so the next zip code is as the second/next row */


I have a lot of tables I don't want to be writing out this out for each zipcode, the code could get really messy. Is there a way to say from zipcode: <--- so like * that start with zipcode union all

Answer Source

If you have a loop going, you can simply do this within it

proc sql;
create table have as
select ...

proc append base=wanted data=have force;

Each time this runs, it will append the contents of have to a master table called wanted