topic Re: Create a table to append everymonth data in SAS Programming

Create a table to append everymonth data

subrat1 — Mon, 27 Jun 2016 14:45:47 GMT

Hi, let us say my january output is:

Month	Policy	Premium
Jan	112	20
Jan	113	23
Jan	114	24

So in next month, i mean in February I will have output:

Month	Policy	Premium
feb	200	45
feb	201	47
feb	202	50

Now I want to creat a table which will append both month data, :

Month	Policy	Premium
jan	112	20
jan	113	23
jan	114	24
feb	200	45
feb	201	47
feb	202	50

But suppose in feb month i run my program 2 times, So it should not append the data twice, I mean it should not generate table like this:

Month	Policy	Premium
jan	112	20
jan	113	23
jan	114	24
feb	200	45
feb	201	47
feb	202	50
feb	200	45
feb	201	47
feb	202	50

SO my point is to append the data only once, after that it should not append for that particular month.

Re: Create a table to append everymonth data

Reeza — Mon, 27 Jun 2016 15:08:47 GMT

Look at the update statement.

If a record is different it updates it. If a record is missing it adds it.

However you need key variables and they should be unique. Otherwise you have to do a manual check before you append the results

Re: Create a table to append everymonth data

Ksharp — Tue, 28 Jun 2016 03:14:43 GMT

add one more sql at the end of your code:

proc sql;

create table want as

select distinct *

from append_table ;

quit;

Re: Create a table to append everymonth data

ChrisNZ — Tue, 28 Jun 2016 03:39:43 GMT

proc append is the fastest by far. Any form of update will be much slower.

To ensure proc append is only called when new data is added, something like this should work:

data ALL;
  MTH='01jan2010'd;
run;

data T2;
  MTH='01feb2010'd;
run;

%macro append_if_new(base=, data=);
%local nobs;
data _null_; 
  if 0 then set ALL nobs=NOBS;
  call symputx('NOBS', NOBS);
data _null_; 
  set ALL(firstobs=&nobs. rename=(MTH=EXISTING_MONTH));
  set T2 (obs=1           rename=(MTH=NEW_MONTH));
  if NEW_MONTH ne EXISTING_MONTH then do;
    putlog "Appending data=&data to base=&base.. ";
    call execute("proc append base=&base data=&data;run;");
  end;
  else putlog "Data=&data already in base=&base..";
run;   
%mend;

%append_if_new(base=ALL, data=T2);
%append_if_new(base=ALL, data=T2);