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

Hi Everyone,

It's my first time question here. I am searching whether there is another way to insert values into a existent table

using data step rather than proc sql like this (proc sql; insert into table values (1); quit;). I would like to know

if is possible to insert a value into a table with no need to recreate this table like in proc sql?

Thanks all.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Look at the MODIFY statement.

proc sql;

insert into tb.want  (code, age) values (3, 27);

quit;

data tb.want;

  modify tb.want;

  code=3;

  age=27;

  output;

  stop;

run;

View solution in original post

12 REPLIES 12
art297
Opal | Level 21

Is this all you are looking to do?:

data want;

  set sashelp.class;

  retain newvariable (1);

run;

Augusto
Obsidian | Level 7

Hi art.

Thanks for your quickly answer.

In fact what i'm looking for is another way to do the following:

data tb.want;

input

     code

     age;

datalines;

1 25

2 26

;

run;

and if i need to insert more values into a want table the only way that i know is doing the following;

proc sql;

insert into tb.want  

(code, age)

values

(3, 27);

quit;

I would like to know if is possible to insert values at the bottom of the file using another way?

art297
Opal | Level 21

You are looking to append additional records to a dataset.  One way would be to create the new data in a datastep and then add them with proc append.

Reeza
Super User

There's a couple of ways. Where will the new data be coming from?

If its in a dataset then proc append or a proc sql insert will work.

proc sql;

insert into have

select code, age from table1;

quit;

I think the order of the variable matters though.

Another option to try is

data want;

set have end=eof;

if eof then do;

code here to insert new records

end;

run;

Augusto
Obsidian | Level 7

Thanks art for your anwswer. It's a good way.

Is there no other way to do this in just one step like in proc sql?.

There is not i guess.

thanks

art297
Opal | Level 21

Sure there is, but it would take longer to run.  The append procedure works the fastest because it doesn't have to process the original file.

Tom
Super User Tom
Super User

Look at the MODIFY statement.

proc sql;

insert into tb.want  (code, age) values (3, 27);

quit;

data tb.want;

  modify tb.want;

  code=3;

  age=27;

  output;

  stop;

run;

Howles
Quartz | Level 8

I think MODIFY is definitely the answer. Tom's code might fail however if the data set initially has no observations. I would move the MODIFY statement to the end, right after the STOP ststement.

Tom wrote:

Look at the MODIFY statement.

proc sql;

insert into tb.want  (code, age) values (3, 27);

quit;

data tb.want;
  modify tb.want;
  code=3;
  age=27;
  output;
  stop;
run;
Augusto
Obsidian | Level 7

Hi Tom, your answer is what i was really looking for.

It's definitely perfect.

Thanks so much all of you.

art297
Opal | Level 21

And I have to add in that I was wrong: proc append is not quicker than quicker than Tom's suggested method. Tom's code actually runs faster than the code I had proposed.

Tom
Super User Tom
Super User

Test for how to append to empty (zero observation) master datasets and also make sure you know what happens if you do not specify values for all variables.

In general you will find that this type of technique is not normally needed (or desired) when processing data with SAS. 

To me it looks more like the solution that someone would propose that was used to using a language that did not really work on datasets (or just SETS like SQL does).  Art's solution using PROC APPEND or other methods to combine your new data into your old data is much more likely to be generally useful.

Doc_Duke
Rhodochrosite | Level 12

Agusto,

One caution about using the Modify statement.  Make sure the original is backed up somewhere first.  A syntax error can make a mess of the dataset.

"Damage                        to the SAS data set can occur if the system terminates abnormally                        during a DATA step that contains the MODIFY statement.                    

Observations in native                        SAS data files might have incorrect data values, or the data file                        might become unreadable. DBMS tables that are referenced by views                        are not affected.                     "

                 

Doc Muhlbaier

Duke

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 835 views
  • 6 likes
  • 6 in conversation