topic Re: Insert Into or Append in SAS Programming

Insert Into or Append

BRM_Work — Mon, 19 Oct 2015 19:03:34 GMT

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

Re: Insert Into or Append

ballardw — Mon, 19 Oct 2015 19:10:29 GMT

What have you tried so far?

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

Re: Insert Into or Append

BRM_Work — Mon, 19 Oct 2015 19:20:17 GMT

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.

Re: Insert Into or Append

Reeza — Mon, 19 Oct 2015 19:24:30 GMT

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

Re: Insert Into or Append

BRM_Work — Mon, 19 Oct 2015 19:44:11 GMT

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?

Re: Insert Into or Append

Patrick — Mon, 19 Oct 2015 21:20:15 GMT

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;

Re: Insert Into or Append

BRM_Work — Wed, 28 Oct 2015 11:58:04 GMT

Thanks, hope to hear from you again.