Insert Into or Append

[BRM_Work]

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

[ballardw]

What have you tried so far?

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

[BRM_Work]

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.

[Reeza]

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

[BRM_Work]

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?

[Patrick · Accepted solution]

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;

[BRM_Work]

Thanks, hope to hear from you again.