How to replace/overwrite values

user_14606164 0
Viewed 71

If I have a table like this called myTable:

        | Var1 | Var2 | Var3 | Var4 | Var5   |
+-------+------+------+------+------+--------+
| Name1 | 1.1  | 1.2  | 0    | 03   | 201805 |
| Name1 | 1.1  | 1.2  | 0    | 03   | 201803 |
| Name2 | 2.1  | 2.2  | 0    | 03   | 202001 |
| Name3 | 3.1  | 3.2  | 3    | 01   | 202002 |

How would I write a query that overwrite/updates all Name1 rows with the Var1 and Var2 values from Name2 given that the rest of the variables matches and only do it on the condition that Var5 is in a desired time interval, like 201805 -> 202001?

sas sql
2 Answers

In SAS Proc SQL the query is a bit arduous.

data have;
input 
  name $ Var1   Var2   Var3   Var4   Var5: yymmn6.; 
format var5 yymmn6.;
datalines;
  Name1   1.1    1.2    0      03     201805  
  Name1   1.1    1.2    0      03     201803  
  Name2   2.1    2.2    0      03     202001  
  Name3   3.1    3.2    3      01     202002  
;

proc sql;
  create table rewrites as 
  select two.var1, two.var2 
  from have as one join have as two
  on one.var3 = two.var3 
   & one.var4 = two.var4
  where one.name = 'Name1' and two.name = 'Name2' 
   & one.var5 between '01may2018'd and '31jan2020'd
   & two.var5 between '01may2018'd and '31jan2020'd
  ;

  update have
  set 
    var1=(select var1 from rewrites)
  , var2=(select var2 from rewrites)
  where name = 'Name1'
   & var5 between '01may2018'd and '31jan2020'd
  ;
quit;

%let syslast = have;
user_12499620

Have a look at the SQL 'LEAD()' function.

There are some nice examples here Example from this URL:

    SELECT 
    month,
    brand_name,
    net_sales,
    LEAD(net_sales,1) OVER (
        PARTITION BY brand_name
        ORDER BY month
    ) next_month_sales
FROM 
    sales.vw_netsales_brands
WHERE
    year = 2018;

Basically you can select the values from the next row.

For the condition on var5, simply put it in the WHERE clause.

user_8969840
Related