Fetch the data for new ID's.

[Sathish_jammy]

Hi Experts,

I need your assistance to get some effective steps to fetch the data for backup.

Please have a look on the below given Sample dataset.

data Branch1;
input Id r_date date9. val1 val2 val3;
format r_date date9.;
cards;
1 18jan1962 12 58 45
4 12feb1993 87 65 32
7 06mar2010 98 54 12
9 08dec2000 32 54 65
6 24sep1999 78 25 69
3 01jul2009 58 47 14
;

data Branch2;
input Id r_date date9. val1 val2 val3;
format r_date date9.;
cards;
81 04aug2012 42 56 89
81 28nov2011 12 26 39
54 06sep2015 18 19 17
67 08jun2018 37 38 39
67 17feb2019 36 45 11
67 27nov2016 84 95 62
19 10oct1998 27 28 29
56 12feb1997 81 82 83
73 14jan2007 97 91 93
73 01may2016 65 12 98
;

My organization have 20 more branches (Branch1, Branch2, ....)

I need to pull newly entered data to my BckUp table from all branches on every month end.

As of now my backup table have few data from these branches. 

BckUp : 

Id	r_date	val1	val2	val3
4	12-Feb-93	87	65	32
7	06-Mar-10	98	54	12
6	24-Sep-99	78	25	69
3	01-Jul-09	58	47	14
81	04-Aug-12	42	56	89
54	06-Sep-15	18	19	17
67	08-Jun-18	37	38	39
19	10-Oct-98	27	28	29
56	12-Feb-97	81	82	83
73	14-Jan-07	97	91	93

There is two conditions need to be process: 

1. update my backup table with the newly entered ID's from branches.

2. update my backup table with the existing ID's which not availed r_date from branches.

I have a method in SQL but that is not more effective. (It take much time/ long query for all branch).

So I'm looking for a effective Data step methods.

Even if you suggest any code with a macro, It would be more helpful for me on every end of the month.

Thanks in advance! 

[RW9 · Accepted solution]

Not really sure I follow you, but I am guessing this bckup dataset should have the branches datasets merged on and any new data added or updated, so perhaps something like;

/* Step 1 set all like data together */
data total;
  set branch:;
run;

/* Step 2 merge the total data to bckup */
/* Assumes both already sorted */
data bckup;
  merge bckup (in=a)
        total (in=b rename=(val1=v1 val2=v2 val3=v3));
  by id r_date;
  if a and b then do;
    flag="Updated";
    val1=v1;
    val2=v2;
    val3=v3;
  end;
  else if a then do;
    flag="No change";
  end;
  else do;
    flag="New data";
    val1=v1;
    val2=v2;
    val3=v3;
  end;
run;

Thats a bit verbose, but trying to show the process clearly.  The in= part is important, as that is how you detemine new/changed.

[Sathish_jammy]

Dear @RW9 , @andreas_lds 

Thank you so much for your help. Your code works well.

If you wouldn't mind, Can u share this code in Macro format.

Much Thanks

[RW9]

Macro is nothing more than a find replace system which creates text.  I see nothing in your spec which has need of macro code to generate anything.  I mean you could put %macro and %mend around it, but other than making the code obfuscated, messier, and less efficient, you would not achieve anything more than is already achieved.

[Sathish_jammy]

That’s very kind of you.

Bunch of Thanks for your valuable comments.

[andreas_lds]

Step 1: append all branch-dataset to form one big dataset (makes further processing easier), Code could be something like:

data all_braches;
  set branch:;
run;

Step 2: Use the update-statement in another data-step (this is non-working code):

data backup;
  update backup all_branches;
  by Id;

  /* more statements for the second condition are required */
run;

Can you post the backup-dataset as data-step? And include at least one example for each condition?

[Sathish_jammy]

data bckup;
input Id r_date date9. val1 val2 val3;
format r_date date9.;
cards;
81 04Aug2012 42	56 89
54 06Sep2015 18 19 17
67 08Jun2018 37 38 39
19 10Oct1998 27 28 29
56 12Feb1997 81 82 83
73 14Jan2007 97 91 93
4  12Feb1993 87 65 32
7  06Mar2010 98 54 12
6  24Sep1999 78 25 69
3  01Jul2009 58 47 14
;

cards; /*need to be update in backup from branches*/
1 18jan1962 12 58 45
9 08dec2000 32 54 65

81 28nov2011 12 26 39
67 17feb2019 36 45 11
67 27nov2016 84 95 62
73 01may2016 65 12 98