topic Re: adding a column for multiple tables in SAS Data Management

adding a column for multiple tables

ilikesas — Thu, 14 Jan 2016 04:22:35 GMT

Hi,

suppose that I have 3 tables: A,B,C.

What I would like to do is for each table to add a new column called "table" and the value in each row of this new column for each table is the name of the table itself.

So table A will have a new column "table" with values a, a, a, a, ....

table B will have a new column named "table" with values b,b,b,...

And so on.

Thank you !

Re: adding a column for multiple tables

Reeza — Thu, 14 Jan 2016 04:26:12 GMT

Why would you want to do this?

Re: adding a column for multiple tables

Reeza — Mon, 18 Jan 2016 15:42:58 GMT

Take advantage of the INDSNAME option to automatically grab the table name.

I'm really curious for the use case of adding this variable, besides when you are setting multiple tables together and even then you can use the INDSNAME option directly.

%macro add_name(dsn);

data &dsn;
set &dsn indsname=source;
Table = scan(source, 2);
run;

%mend;

%add_name(tableA);
%add_name(TableB)

Re: adding a column for multiple tables

ilikesas — Thu, 14 Jan 2016 04:33:59 GMT

Hi Reeza,

I have many tables of stocks. Each table is named after the stock and has two columns: "date" and "daily price".

In order to perform my analysis I need to merge all the tables together into one table, but since there is no indicator which date and price is for which stock, the analysis won't be possible.

Thank you!

Re: adding a column for multiple tables

Reeza — Thu, 14 Jan 2016 04:38:14 GMT

I did put the answer above, under the spoiler tag on how you could do it via a macro. The solution below is what you really want though, which is what I suspected.

data want;
set stock1 stock2 stock3 stock4 indsname=source;
table=scan(source, 2);
run;

Re: adding a column for multiple tables

ilikesas — Thu, 14 Jan 2016 05:01:25 GMT

Thank you Reeza,

I got the answer that I needed!!!

Indsname happens to be a very useful function, I didn't even know it existed.

Thank you again!!!

Re: adding a column for multiple tables

lissacoffey — Mon, 18 Jan 2016 15:32:42 GMT

The solution above is what i really wanted