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

Hi, let us say my january output is:

MonthPolicyPremium
Jan11220
Jan11323
Jan11424

 

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

MonthPolicyPremium
feb20045
feb20147
feb20250

 

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

MonthPolicyPremium
jan11220
jan11323
jan11424
feb20045
feb20147
feb20250

 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:

MonthPolicyPremium
jan11220
jan11323
jan11424
feb20045
feb20147
feb20250
feb20045
feb20147
feb20250

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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);

 

View solution in original post

3 REPLIES 3
Reeza
Super User

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  

Ksharp
Super User

add one more sql at the end of your code:

 

proc sql;

create table want as

 select distinct *

  from append_table ;

quit;

ChrisNZ
Tourmaline | Level 20

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);

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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
  • 3 replies
  • 1499 views
  • 1 like
  • 4 in conversation