Hi Larry, I think First Method should work for me. I just tested it with dummy data and it worked fine, however I will do it on original database and test on all complex queries, data consistensies and all. thanks you so much for the help. Just to mention, we tried with secoind method but it involved writing query for each of the value column which didn't fit our requirement and was too tedious. Anyways, I will ask any further question if doubt arises. thanks Gnyanendra ... View more

Hi Larry, My mistake in notwriting it clearly. in (a), I meant I need to UPDATE older record with new one if KEY is same. below text should help you get the problem better. thanks! ---------------------- Hereis the SQL code I’m using: PROC SQL; INSERT INTO BETA.secondary1 (a.id, name, code, status) SELECT t1.a.id, t1.name, t1.code, t1.status FROM BETA.primary1 t1 WHERE status = ‘1’; QUIT; I now want toUPDATE table BETA.secondary1 again as the status changes to ‘0’ for which I’mtrying UPDATE clause instead INSERT with different combinations of SELECT Like UPDATE secondary1 (a.id, name, code, status) SELECT t1.a.id, t1.name, t1.code, t1.status FROM BETA.primary1 t1 WHERE status = ‘0’; This is a simple query, I have lot more and complex which Ican’t post but I think you get the idea. Here is the error log 1          ;*';*";*/;quit;run; 2          OPTIONS PAGENO=MIN; 3          %LET _CLIENTTASKLABEL='Program'; 4          %LET _CLIENTPROJECTPATH=''; 5          %LET _CLIENTPROJECTNAME=''; 6          %LET _SASPROGRAMFILE=; 7          8          ODS _ALL_ CLOSE; 9          OPTIONS DEV=ACTIVEX; 10         GOPTIONS XPIXELS=0 YPIXELS=0; 11         FILENAME EGSR TEMP; 12         ODS tagsets.sasreport12(ID=EGSR)FILE=EGSR STYLE=Analysis 12       !STYLESHEET=(URL="file:///C:/sas93/software/x86/SASEnterpriseGuide/4.3/Styles/Analysis.css") 12       ! NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8options(rolap="on"); NOTE: WritingTAGSETS.SASREPORT12(EGSR) Body file: EGSR 13         14         GOPTIONS ACCESSIBLE; 15         PROC SQL; 16           UPDATE BETA.secondary1 (a.id, name,code, status)                                          _                                          79                                          76 ERROR 79-322: Expecting a ). ERROR 76-322: Syntax error, statementwill be ignored. 17                                SELECT 18                                       t1.a.id, 19                                       t1.name, 20                                       t1.code, 21                                       t1.status 22 23 24                                FROMBETA.primary1 t1 25                                WHEREstatus = ‘1’; NOTE: PROC SQL set option NOEXECand will continue to check the syntax of statements. 26         QUIT; NOTE: The SAS System stoppedprocessing this step because of errors. NOTE: PROCEDURE SQL used (Totalprocess time):       real time           0.00 seconds       cpu time            0.00 seconds       27         28         GOPTIONS NOACCESSIBLE; 29         %LET _CLIENTTASKLABEL=; 30         %LET _CLIENTPROJECTPATH=; 31         %LET _CLIENTPROJECTNAME=; 32         %LET _SASPROGRAMFILE=; 33         34         ;*';*";*/;quit;run; 2                                           TheSAS System            06:41 Thursday,January 12, 2012 35         ODS _ALL_ CLOSE; 36         37         38         QUIT; RUN; 39         ... View more