BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BRM_Work
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

6 REPLIES 6
ballardw
Super User

What have you tried so far?

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

BRM_Work
Calcite | Level 5

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
Super User
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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5
Thanks, hope to hear from you again.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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