|Date: ||Tue, 26 May 2009 15:43:23 -0400|
|Reply-To: ||Sigurd Hermansen <HERMANS1@WESTAT.COM>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Sigurd Hermansen <HERMANS1@WESTAT.COM>|
|Subject: ||Re: Data Step+ Proc SQL|
|Content-Type: ||text/plain; charset="us-ascii"|
I'll stick with my standard response: it's a database design problem. A programmer can program around the problem, but why make it difficult.
As I understand the situation, your newjobs table (temp) really doesn't have a correct lastcoderun attribute. The main jobs table (temp1) has a lastcoderun column that you hope to update when you insert the job type and date of new jobs. To do that, you'll need to join temp with temp1 on job and select the maximum date of the same type of job as lastcoderun. So temp should consist of only the job and coderun attributes. A temporary table should combine job, coderun, and the maximum lastcoderun value for each job type. You can then use the temporary table to insert new values into temp1.
As an aside, do you ever insert multiples of the same job type from one temp table? If so, you would be making lastcoderun indeterminate.
Perhaps you should take a deep breath, step back, and carefully write out what you are trying to do. I'd reconsider table names such as temp and temp1 as a first step. Who knows what they represent? Try to find table and attribute names that mean something to others and will mean something to you when you try to revise the program six months from now. You should also state in a few words what you are hoping the program will produce. Since you already have lstcoderun in one table, why do you need it in another. Why not extract it as needed for a report or other display?
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of SUBSCRIBE SAS-L Joe H. Smith
Sent: Monday, May 25, 2009 11:38 AM
Subject: Data Step+ Proc SQL
Here is my code
format coderun lastcoderun dtdate9.;
now i am trying to insert this values into other table.
insert into table temp1 (jobname,coderun,lastcoderun)
select values job,coderun,lastcoderun from temp;
if the output of temp1 table is thisway:
job coderun lastcoderun
Updating 25May2005 25May2005
if i keep running the same above jobs after evary month my output should have these values .
i want the values of lastcoderun to be previous value and the coderun value to be current value ..how should i get this o/p
job coderun lastcoderun
Updating 25June2009 25May2009
Inserting 25Jul2009 25Jun2009
Deletion 25Sep2009 25Jul2009
Creation 25Oct2009 25Sep2009
I am unable to write code to store previous value of a variable and then populate it to new table. Hope i Have confused ,but still understand from the outputs mentioned .
Thanks In Advance.