DATA Step, Macro, Functions and more

Insert Into or Append

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Insert Into or Append

Newbie can't write an Insert into or an append query to input five records  two columns into a two column table. Help.


Accepted Solutions
Solution
‎10-28-2015 07:57 AM
Respected Advisor
Posts: 4,173

Re: Insert Into or Append

[ Edited ]

Are these tables in SAS or in a database?

 

If it's SAS then don't use a Delete as it only deletes the records logically and your table (the actual file) would be growing constantly.

 

With SAS you re-create a table. If you want to maintain the table structure with no rows but all the columns and their attributes then use the following code.

/* option 1 */
data ShipAlt.PRICINGINFO;
stop;
set ShipAlt.PRICINGINFO;
run;

/* option 2 */
data ShipAlt.PRICINGINFO;
set ShipAlt.PRICINGINFO (obs=0);
run;

 

With tables in a database: Don't use a normal SQL DELETE but a TRUNCATE in pass-through SQL as this is much faster (syntax will depend on the database).

 

For appending data to a SAS table: Proc Append or the SQL OUTER UNION CORR.

proc append base=ShipAlt.PRICINGINFO data=WORK.MECH_PRICING;
run;
proc append base=ShipAlt.PRICINGINFO data=WORK.DLA_PRICING;
run;

 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Insert Into or Append

What have you tried so far?

Have you been getting error messages? If so, what are they.

New Contributor
Posts: 4

Re: Insert Into or Append

Can't seem to get this started I tried insert into and append didn't save the code I am also new to this forum.

 

PROC SQL;

CREATE TABLE ShipAlt.PRICINGINFO AS

SELECT * FROM WORK.MECH_PRICING

OUTER UNION CORR

SELECT * FROM WORK.DLA_PRICING

;

Quit;

 

My predecessor wrote the following I would prefer to empty this and append the new dataset and I have a temp dataset of five values I need to append to Princinginfo. I am just a bit lost working with the datatypes. The first column of PrincingInfo is a $Char9 field and the second  i believe is an 11.1 I just need to input a currency or a numeric value of whole numbers and a two point decimal.

 

 

Super User
Posts: 19,789

Re: Insert Into or Append

What do you mean by "empty this"?

Your fields do need to be the same time, so you'll have to convert it somehow. You can convert it in a separate step and then consider using PROC APPEND to append the data. Another option is to change the data type and use a INSERT INTO from PROC SQL. The documentation/sample for a PROC SQL insert into is fairly clear.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001387930.htm

In your select statement you can do the conversion using a PUT function.

select put(column1, $9.) as column1, column2
from PRICINGINFO
New Contributor
Posts: 4

Re: Insert Into or Append

i think i'd like to try something like the following instead of creating a new table each time?

 

 

Proc SQL;

DELETE * FROM ShipAlt.PricingInfo;

Proc SQL;

INSERT INTO ShipAlt.PricingInfo

 

/* with applicable code to input his code created a new table each time didn't it?

 

SELECT DISTINCT t1.NIIN, t2.STD_PRICE

FROM SHIPALT.FMPMIS t1

INNER JOIN CROSSREF.V_ITEMS_MECH t2 ON (t1.NIIN = t2.NIIN)

ORDER BY t1.NIIN;

 

/* this code created a new table each time and it seems to inheirit the datatypes and lengths from the FMPMIS table I will try to employ your put statement for the string value of nine characters but how to format a whole number and two position decimal in the second column of PrincingInfo?

 

Solution
‎10-28-2015 07:57 AM
Respected Advisor
Posts: 4,173

Re: Insert Into or Append

[ Edited ]

Are these tables in SAS or in a database?

 

If it's SAS then don't use a Delete as it only deletes the records logically and your table (the actual file) would be growing constantly.

 

With SAS you re-create a table. If you want to maintain the table structure with no rows but all the columns and their attributes then use the following code.

/* option 1 */
data ShipAlt.PRICINGINFO;
stop;
set ShipAlt.PRICINGINFO;
run;

/* option 2 */
data ShipAlt.PRICINGINFO;
set ShipAlt.PRICINGINFO (obs=0);
run;

 

With tables in a database: Don't use a normal SQL DELETE but a TRUNCATE in pass-through SQL as this is much faster (syntax will depend on the database).

 

For appending data to a SAS table: Proc Append or the SQL OUTER UNION CORR.

proc append base=ShipAlt.PRICINGINFO data=WORK.MECH_PRICING;
run;
proc append base=ShipAlt.PRICINGINFO data=WORK.DLA_PRICING;
run;

 

New Contributor
Posts: 4

Re: Insert Into or Append

Thanks, hope to hear from you again.
🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 1385 views
  • 0 likes
  • 4 in conversation