Make insert using data set rather than proc sql; with no needs to create another data set file;

[Augusto]

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.

[art297]

Is this all you are looking to do?:

data want;

  set sashelp.class;

  retain newvariable (1);

run;

[Augusto]

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]

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]

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]

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]

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 · Accepted solution]

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]

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]

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

It's definitely perfect.

Thanks so much all of you.

[art297]

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]

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]

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