topic appending / inserting into a data table in SAS Programming

appending / inserting into a data table

deleted_user — Wed, 04 Mar 2009 17:52:55 GMT

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

Re: appending / inserting into a data table

sbb — Wed, 04 Mar 2009 18:32:26 GMT

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

Scott Barry
SBBWorks, Inc.

Re: appending / inserting into a data table

deleted_user — Wed, 04 Mar 2009 19:29:31 GMT

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

Re: appending / inserting into a data table

sbb — Wed, 04 Mar 2009 21:51:50 GMT

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.

Re: appending / inserting into a data table

Peter_C — Wed, 04 Mar 2009 22:18:02 GMT

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

Re: appending / inserting into a data table

deleted_user — Thu, 05 Mar 2009 13:48:15 GMT

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

Re: appending / inserting into a data table

Peter_C — Thu, 05 Mar 2009 13:57:35 GMT

Jerry

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

PeterC

Re: appending / inserting into a data table

deleted_user — Thu, 05 Mar 2009 14:34:43 GMT

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

Re: appending / inserting into a data table

GertNissen — Fri, 06 Mar 2009 13:15:55 GMT

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]

Re: appending / inserting into a data table

deleted_user — Fri, 06 Mar 2009 14:42:19 GMT

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

Re: appending / inserting into a data table

deleted_user — Fri, 06 Mar 2009 14:48:41 GMT

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

Thanks

appending / inserting into a data table

Eva — Wed, 01 Feb 2012 10:12:08 GMT

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

appending / inserting into a data table

Hima — Wed, 01 Feb 2012 20:53:43 GMT

PROC APPEND BASE = TABLEA DATA=TABLEB;

RUN;

Re: appending / inserting into a data table

LinusH — Thu, 02 Feb 2012 10:09:46 GMT

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