cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
WaiLoon
‎01-18-2023
  • 11 Posts
  • 1 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-30-2022 10:03 PM
‎09-30-2022 10:03 PM
Sorry, I didnt make it clear. I wish to proc append the join temp table to postgresql as a temp table and not physical table in SAS.  ... View more

Re: Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-30-2022 08:45 AM
‎09-30-2022 08:45 AM
Hi I got 1 more question. I create a tmptable and join few column from few different table, then copy this tmptable to postgresql.    CREATE TABLE Temp1 AS SELECT HW.Id, HW.Name, HW.Status, Type.TypeName FROM source.HW INNER JOIN source.Type ON Type.TypeID = HW.TypeID ; proc datasets library = source; append base = SASHardwareTable data = Temp1 ; run; quit; ... View more

Re: Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-30-2022 02:26 AM
‎09-30-2022 02:26 AM
Hi Then may I know how to delete the temp table?? I plan to do 1. Insert whatever new record from SAS to postgresql using normal "proc sql" 2. use "proc append" to copy the table from SAS to postgresql as a temp table 3. then use "execute" to run normal SQL statement to do the data update  4. delete the temp table from postgresql   but i'm not sure how to do the delete like the "proc append".   And also wish to know if i use "proc append" to copy a temp table to postgresql instead of physical table?  example I create a tmptable and join few column from few different table, then copy this tmptable to postgresql.  ... View more

Re: Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-29-2022 08:07 PM
‎09-29-2022 08:07 PM
Hi @SASKiwi  Just to make sure i understand correctly   1. Create a temp table (tmpHW) in postgresql 2. Insert source table to the tmpHW 3. Update the tmpHW data to Hardware table using SQL   Is this the steps?  and how about the append you mentioned?    Sorry, i really need help on this cause really dont know SAS code. The code you show me look very simple and it create the temp table within few seconds.   I would like to understand, how do i insert new record from the tmpHW to Hardware and also Update the data from tmpHW to Hardware using SAS code??  Can show some sample??    It is possible i prepare the temp data table in proc sql... then from proc datasets append the data table to postgresql?   ... View more

Re: Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-29-2022 01:19 AM
‎09-29-2022 01:19 AM
Hi Thanks for the reply. Actually I can't do that, reason is the target table contain data from source and also user input value. Some fields are updating from SAS, but some fields are user input from the web ui. so I can't delete the record.   Btw, how to do the Proc Dataset with Append? I'm new to SAS, I'm only good at SQL, so now sure how to do that...  ... View more

Re: Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-28-2022 11:17 PM
‎09-28-2022 11:17 PM
Hi Thanks for fast reply. Yes, I'm updating to PostgreSQL from SAS.   Temp table means physical table or just table in memory??  Do you have sample or details??    How I do now is, I create a tmp table with all data I need from SAS table Then I have 1 extra column call InsertUpdate, this column will differentiate whether the record is new or existing. then Insert into PostgreSQL for InsertUpdate = 'I' and update data if InsertUpdate = 'U'. I have create index key in postgreSQL for the updating but still very slow.  ... View more

Best way to do SQL Update from another table

by Calcite | Level 5 in SAS Programming
‎09-28-2022 09:46 PM
‎09-28-2022 09:46 PM
Hi  I need to update my current working table from source database table.   Update HardwareList tgt SET     Remark = (SELECT Remark FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')            ,Status = (SELECT Status FROM TmpHW WHERE tgt.ID= TmpHW.ID and InsertUpdate = 'U')            ,DecomDate = (SELECT DecomDate FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')            ,DecomReason = (SELECT DecomReason FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')        I need to have 1 select statement for each field that I need to update, if i got 30 fields need to update means I need 30 select statement and I worry this will cause the performance issue cause too many extraction.   Is there any best method to do update?    Other database system like MSSQL, MySQL, PostgreSQL, the update statement can be very simple example Update HardwareList tgt SET     Remark = Remark            ,Status = Status            ,DecomDate = DecomDate             ,DecomReason = DecomReason FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U'   I really having issue with the performance for the update, and I really hope to see if there are any way to improve it.  ... View more

Re: How to get last day of previous month and first day for current fo...

by Calcite | Level 5 in SAS Programming
‎09-28-2022 09:41 PM
‎09-28-2022 09:41 PM
I finally get it works, so date and datetime having different handling... it is very different from the normal SQL...  ... View more

Re: How to get last day of previous month and first day for current fo...

by Calcite | Level 5 in SAS Programming
‎09-26-2022 04:35 AM
‎09-26-2022 04:35 AM
Hi, thanks for suggestions, and sorry for the late reply cause suddenly got some production issue need to resolve.    I tried the suggested syntax but still not able to get correct date.   %let first_day_curr_month = %sysfunc(intnx(month,%sysfunc(today()),0,b)); %let last_day_prev_month = %eval(&first_day_curr_month. -1); UPDATE HardwareList tgt SET DateDecommissioned = &last_day_prev_month WHERE NOT EXISTS (.......);   I check the data, the DateDecommissioned  is 1960-01-01 06:21:28 Anything i did wrong?    ... View more

Re: How to get last day of previous month and first day for current fo...

by Calcite | Level 5 in SAS Programming
‎09-13-2022 10:06 AM
‎09-13-2022 10:06 AM
Thanks. But what if I need to have yyyy, how should I format?? ... View more

How to get last day of previous month and first day for current for De...

by Calcite | Level 5 in SAS Programming
‎09-13-2022 08:23 AM
‎09-13-2022 08:23 AM
Equipment Data Voltage Master Project AIPM workflow   Hoza by BA level User access by State/Hoza/BA GIS Data     Hi all My program getting hardware list from another system, but the system don't have CommissionDate and DecommissionDate. Source system is in Excel and Target is PostgreSQL.  Therefore I need to generate these 2 value every month when ETL the hardware list. So, what I planned to do is, if the hardware from source DB not in the target DB, then I will insert and set the CommissionDate to 1st of current month. If the hardware in target DB not in source DB, then I will set the DecommissionDate to last day of previous month. I tried many way but unfortunately can't get it work. I can't even get the previous month work. But today I tried this %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), yymmdd8.); %put &Decomm; And I get 276 %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), yymmdd8.); 277 %put &Decomm; SYMBOLGEN: Macro variable DECOMM resolves to 22-08-13 22-08-13 it seems like is I manage to get previous month, but I need the day to be last day of the month and also the format should be dd/mm/yyyy.   Today I tried this syntax %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), ddmmyyyy8.); %put &Decomm;   and I get this 276 %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), ddmmyyyy8.); 277 %put &Decomm; SYMBOLGEN: Macro variable DECOMM resolves to 22870 22870   Other than above, i also need to get the first day of current month... May I know what is my script missed?? ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎01-18-2023 02:31 AM
Likes given to
View All
Quick links