Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

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

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); quitSmiley Wink. 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.


Accepted Solutions
Solution
‎10-27-2011 04:28 PM
Super User
Super User
Posts: 7,042

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

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


All Replies
PROC Star
Posts: 7,471

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

Is this all you are looking to do?:

data want;

  set sashelp.class;

  retain newvariable (1);

run;

Frequent Contributor
Posts: 80

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

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?

PROC Star
Posts: 7,471

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

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.

Super User
Posts: 19,789

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

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;

Frequent Contributor
Posts: 80

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

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

PROC Star
Posts: 7,471

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

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.

Solution
‎10-27-2011 04:28 PM
Super User
Super User
Posts: 7,042

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

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;

Regular Contributor
Posts: 184

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

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;
Frequent Contributor
Posts: 80

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

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

It's definitely perfect.

Thanks so much all of you.

PROC Star
Posts: 7,471

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

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.

Super User
Super User
Posts: 7,042

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

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.

Trusted Advisor
Posts: 2,115

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 177 views
  • 6 likes
  • 6 in conversation