topic Re: Make insert using data set rather than proc sql; with no needs to create another data set file; in SAS Procedures

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

Augusto — Thu, 27 Oct 2011 18:42:01 GMT

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.

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

art297 — Thu, 27 Oct 2011 18:47:33 GMT

Is this all you are looking to do?:

data want;

set sashelp.class;

retain newvariable (1);

run;

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

Augusto — Thu, 27 Oct 2011 19:05:30 GMT

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?

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

art297 — Thu, 27 Oct 2011 19:08:58 GMT

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.

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

Reeza — Thu, 27 Oct 2011 19:10:22 GMT

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;

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

Augusto — Thu, 27 Oct 2011 19:15:09 GMT

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

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

art297 — Thu, 27 Oct 2011 19:23:22 GMT

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.

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

Tom — Thu, 27 Oct 2011 20:28:06 GMT

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;

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

Howles — Thu, 27 Oct 2011 21:39:42 GMT

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;

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

Augusto — Fri, 28 Oct 2011 11:56:46 GMT

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

It's definitely perfect.

Thanks so much all of you.

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

art297 — Fri, 28 Oct 2011 13:29:43 GMT

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.

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

Tom — Fri, 28 Oct 2011 13:53:45 GMT

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.

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

Doc_Duke — Fri, 28 Oct 2011 13:56:56 GMT

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