appending / inserting into a data table

[deleted_user]

What is the best way using a data set to append data to an existing data set?

If I have
table A
NAME AGE
Bob 33
Fred 40

now I want to add all the rows from a different data set into table A.

table B
NAME AGE
William 74
Dan 29

After append/insert

table A
NAME AGE
Bob 33
Fred 40
William 74
Dan 29

[sbb]

One approach is to use a DATA step and a SET statement with the two files specified on the SET.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Scott,
That works but my column names have to match up and they don't. Do I have to do a rename on the columns that don't? Is there a different way to do it or is this the cleanest and fastest way?

thank you again for your help

Jerry

[sbb]

You'll want to validate your input tables using PROC CONTENTS, and then use RENAME= on the SET statement when combining your files.

Scott Barry
SBBWorks, Inc.

[Peter_C]

sql union operator without CORRESPONDING may be able to achieve what you want, but if the internal (varnum) order is not going to line up the variables as you want, then you'll need to list the columns of one table in the order you need to line up with the other table.
And, of course, you probably need the ALL option of UNION. See
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473694.htm .

good luck

PeterC

[deleted_user]

Peter thanks for the reply but I can't use proc sql it has to be a data step.
Scott I did the following to get around it
In one data table I had a column defined as character and the table i'm joining too has it as numeric so I had to do this to get around it and it works.

data temp ;
set temp2 ;
idnumeric = input(id,best.) ;
rename id=idchar ;
rename idnumeric = id ;
run ;

Thank you
Jerry

[Peter_C]

Jerry

why can't you use PROC SQL for this problem ?

PeterC

[deleted_user]

Boss wants data steps only. Since i'm new he says it will help me learn more about sas.

[GertNissen]

Don't use datasteps for appending data sets - It will read every record from both datasets making it a very slow proces. It requires that your datatypes are the same - or look at the FORCE option.

[pre]data table_B;
input NxME $ xGE;
datalines;
William 74
Dan 29
;run;

data table_A;
input NAME $ AGE;
datalines;
Bob 33
Fred 40
;run;

proc append
data=table_b(rename=(nxme=name xge=age))
base=table_a;
run;[/pre]

[deleted_user]

Yep, if your boss wants you to learn more about programming efficiently then he'll be looking for proc append.

[deleted_user]

pznew,
Thanks for the tip. I finally got it working in a data step, but I want to try the append.

Thanks

[LinusH]

Well, PROC APPEND is not a data step...

To be able to rename columns with proc append, you need to use the rename data set option, otherwise you'll loose data.

I like the syntax of SQL better for this case, the select clause of the insert statement lets you list which columns to insert, and is does not care for names, just position and data type.

And is almost as efficient as append.

/Linus

[Eva]

Hi Zineg,

thanx for the performance advice regarding proc append/data step. I always use proc append but never had a thought on whether it was faster that the data step solution 🙂

Best wishes

Eva

[Hima]

PROC APPEND BASE = TABLEA DATA=TABLEB;

RUN;