Insert into table from a data set

Reply
Occasional Contributor
Posts: 8

Insert into table from a data set

I have to check one table and insert into another table. and this should be performed for around 1000 entries in the source table.

the source table is grouped according to certain criteria and I have a column that should be saved to another table when a particulat condition meets.

and this should be performed for around 1000 data entries

I planned to take data set since I want to get it done automatically for 1000+ entries. So as I understand, if I write a condition in data set it will applicable for each entry in it.

Therefore I have a plan to realise this way.

Data Grouped_all;

set Grouped_all;

If Date="&mon1" and Acct="Email" and Type="0"

then

.

.

.

update destination_table.field=Source_table.Count, destination_table.name=source_table.name;
and the condition is checked from source table that means date, acct, type are variablesi n source table.

and for each of the entries these conditions should be checked and saved in the other table.

any suggestions, Ideas??

thanks for the help

Super User
Super User
Posts: 6,500

Re: Insert into table from a data set

It depends on what you mean by "insert".   A normal SAS processing way would be the create the table of values to insert as one step and then use appropriate tool to insert the records.  For example if the source and target datasets use the same structure then you might do something like.

data add ;

  set source_table;

  If Date="&mon1" and Acct="Email" and Type="0" ;

run;

proc append base= destination_table data=add;

run;

If instead you are talking about changing just selected variables for observations that are already in the destination table then you probably better off using the UPDATE statement of  PROC SQL .

Occasional Contributor
Posts: 8

Re: Insert into table from a data set

Hi Tom, thanks for the reply. by insert I mean to add certain value to an existing table with fields already defined.

I am fine with the code taht you gave, but I need to insert it to a particular field/variable. you got my point?  after running

data add;

set source_table

if....

run;

the result is a number which I need to insert into a particular field in destination table.

Proc append base=destination_table (where can I give the table field where I need to insert it?)data=add;

thanks for the help and awaiting a suggestion.


Super User
Super User
Posts: 6,500

Re: Insert into table from a data set

In general it is better to think of the problem as one of combining tables rather than operating on single observations like you would a procedural language like C or FORTRAN.

Examples will work better.

If we take the SASHELP.CLASS dataset and say we want to update the age of one of the students.  

* Get a copy of SASHELP.CLASS ;

data class ;

set sashelp.class ;

run;

Say we have a new dataset with NAME and AGE variables.

* Create the table with the replacement values ;

data updates;

set class ;

if name='Alfred';

age=15;

keep name age;

run;


Then there are many ways to combine the two to create the newly required table.

The simplest is to just recreate the dataset by merging on the new information.

data class;

   merge class updates;

   by name;

run;

If you really do need to update the variable in place (perhaps the file is huge or it actually lives in an external database like Oracle) then you can use the UPDATE functionality of SQL code by using PROC SQL.  I am not that great with SQL but here is an example.

proc sql ;

update class

   set age=(select age from updates where class.name=updates.name)

   where name in (select name from updates)

;

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 646 views
  • 1 like
  • 2 in conversation