08-27-2013 08:46 AM
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.
If Date="&mon1" and Acct="Email" and Type="0"
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
08-27-2013 09:26 AM
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 ;
If Date="&mon1" and Acct="Email" and Type="0" ;
proc append base= destination_table data=add;
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 .
08-30-2013 10:41 AM
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
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.
08-30-2013 11:21 AM
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 ;
Say we have a new dataset with NAME and AGE variables.
* Create the table with the replacement values ;
set class ;
keep name age;
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.
merge class updates;
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 ;
set age=(select age from updates where class.name=updates.name)
where name in (select name from updates)