Macro for adding rows in multiple tables

[NP2212]

Hello
I want to add 4 columns in multiple tables. What is easiest method for it?
Is single macro can create in all tables?
Thanks for help

[Kurt_Bremser]

Rows (as in your subject line) or columns?

Which values should the new objects contain? Only missing, or taken from other datasets or calculated from existing values within the dataset?

In the case of variables (columns), what should the types and other attributes be?

Are all datasets in a single library, or spread out over multiple libraries? Are there patterns for the selection of datasets?

Anyway, start with the code for changing one dataset. Determine which parts of the code need to be changed for other datasets, then make them dynamic through the use of macro variables. How you proceed from there depends quite a lot on the answers to the questions above.

[NP2212]

I want to add columns not rows. Values for another columns from other datasets. Variable type remains same but all variables from different datasets. All datasets are in different library. Can You suggest for above conditions? and for changing certain number of tables how much time taken? (approximately 50 tables) 

[ballardw]

---

@NP2212 wrote:

I want to add columns not rows. Values for another columns from other datasets. Variable type remains same but all variables from different datasets. All datasets are in different library. Can You suggest for above conditions? and for changing certain number of tables how much time taken? (approximately 50 tables) 

---

Which now brings up the question of which row to do you want which column value assigned to.

If you have  variable, or combination of variables, that can be used to match values then this may be next to trivial or a nightmare depending on whether your identification variables have duplicates in one or more of the "other datasets".

If there are no duplicates of the combination of identification variables then either a Data step Merge or SQL Join could be used. But duplicates can mean more work.

A simple MERGE EXAMPLE: Note that data step merge requires that the sets be sorted by the Id variable(s) by default.

data one;
   input id a b;
datalines;
1 1 11
2 2 22
3 3 33
;

data two;
   input id c;
datalines;
1 111
2 222
3 333
;

data three;
   input id d;
datalines;
1 1111
3 3333
;

data want;
   merge one two three;
   by id;
run;

To see what duplicates can cause problems with this approach make 2 or more, differing number, of rows with different values of a, b, c or d but the same Id in 2 of the sets. Note: I intentionally did not provide an Id=2 in the last set to show what happens:

Sql

proc sql;
   create table want as
   select t1.id, t1.a, t1.b, t2.c, t3.d
   from one as t1
        left join
        two as t2
        on t1.id=t2.id
        left join
        three as t3
        on t1.id=t3.id
   ;
quit;

Also modify the sets to have duplicates of the ID to see what happens.

I used LEFT join which is just one way of combining these. You really should provide example data, starting and desired result because there are many ways to combine them and you likely don't want all of them.

[s_lassen]

There are several possibilities. You can use a macro, you can use CALL EXECUTE, or you can write code to a temporary SAS file. I think my favorite for this is the last one, e.g.:

filename tempsas temp;
data _null_;
  length tablename $60;
  input tablename;
  file tempsas;
  put 'Alter table ' tablename 'add <rest of SQL statement here>;';
Cards;
work.mydata
outdata,gg_dates
outdata.customers
:run;

Then open the TEMPSAS fileref, and see what the code looks like. If it looks right, you can try submitting this:

proc SQL;
%include tempsas/source2;
quit;

[ballardw]

By "table" do you mean a data set? Or a Report?

What content should be in the rows?

Note that "adding columns" rebuilds a data set so is likely not to be a trivial exercise. Rows can be appended to existing data but some example of variables or what is going on is sort of important. If you are adding different values to different sets then examples really are needed.

---

@NP2212 wrote:
Hello
I want to add 4 columns in multiple tables. What is easiest method for it?
Is single macro can create in all tables?
Thanks for help

---