SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

adding a column for multiple tables

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

adding a column for multiple tables

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 ! 


Accepted Solutions
Solution
‎01-14-2016 12:01 AM
Super User
Posts: 17,745

Re: adding a column for multiple tables

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;

View solution in original post


All Replies
Super User
Posts: 17,745

Re: adding a column for multiple tables

Why would you want to do this?

Super Contributor
Posts: 413

Re: adding a column for multiple tables

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!

Solution
‎01-14-2016 12:01 AM
Super User
Posts: 17,745

Re: adding a column for multiple tables

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;
Super Contributor
Posts: 413

Re: adding a column for multiple tables

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!!!

New User
Posts: 1

Re: adding a column for multiple tables

The solution above is what i really wanted

Super User
Posts: 17,745

Re: adding a column for multiple tables

[ Edited ]

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) 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 360 views
  • 0 likes
  • 3 in conversation