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.
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);
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
add one more sql at the end of your code:
proc sql;
create table want as
select distinct *
from append_table ;
quit;
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);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.