DATA Step, Macro, Functions and more

appending / inserting into a data table

Reply
N/A
Posts: 0

appending / inserting into a data table

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
Super Contributor
Super Contributor
Posts: 3,174

Re: appending / inserting into a data table

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

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: appending / inserting into a data table

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
Super Contributor
Super Contributor
Posts: 3,174

Re: appending / inserting into a data table

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.
Valued Guide
Posts: 2,174

Re: appending / inserting into a data table

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
N/A
Posts: 0

Re: appending / inserting into a data table

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
Valued Guide
Posts: 2,174

Re: appending / inserting into a data table

Jerry

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

PeterC
N/A
Posts: 0

Re: appending / inserting into a data table

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

Re: appending / inserting into a data table

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]
N/A
Posts: 0

Re: appending / inserting into a data table

Yep, if your boss wants you to learn more about programming efficiently then he'll be looking for proc append.
N/A
Posts: 0

Re: appending / inserting into a data table

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

Thanks
Super User
Posts: 5,255

Re: appending / inserting into a data table

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

Data never sleeps
Regular Contributor
Regular Contributor
Posts: 156

appending / inserting into a data table

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

Regular Contributor
Posts: 233

appending / inserting into a data table

PROC APPEND BASE = TABLEA DATA=TABLEB;

RUN;

Ask a Question
Discussion stats
  • 13 replies
  • 339 views
  • 2 likes
  • 7 in conversation