Hi everyone. Good Day. I have 2 tables, 1 source table and 1 target table. The source table has some columns that are also in target table. Meaning to say, source table is mapped to target table. I will be using SCD Type 1 loader to load data simply because i want to replace some of the columns(data) in the target dimension table. The key for SCD Type 1 loader is "Id". Target table will have its own Id(incremental numbers) while source table doesn't have Id column. Therefore, when we use SCD Type 1 loader, it will compare "Id" in source and target before replacing the values in target table. Hence, I have wrote a script(Hash object lookup) to do lookup(Source and target) and add Id for each row of looked-up . Problem is, I am unclear on how to control the ID+1. Lets have a look at my script: %let maxId=0;
proc sql noprint;
select max(0,max(Id)) into :maxId
from postgres.targettable;
quit;
%put Maximum Id: &maxId;
data work.ncp_asset_re_toload ;
attrib ReCapacity length=8;
attrib Id length=8;
if _n_=1 then do;
declare hash h(dataset:"postgres.targettable (rename=(CustomerNumber=re_ca_no CustomerName=re_customer_name))");
h.definekey('re_ca_no','re_customer_name');
h.definedata('DeclaredGenerationMd','ReCapacity','Id');
h.definedone();
end;
set work.ncp_asset_re;
rc1=h.find();
if (rc ne 0) then do;
DeclaredGenerationMd = re_highest_md_recorded_kw;
ReCapacity=Re_Capacity;
FeederNo=re_switch_no;
end;
maxId+1;
Id=maxId;
output;
run; For those that can be mapped, Id will also be populated(Meaning Id from target table is populated). However, for those that cannot be mapped using hash object, I want to assign ID for these records with the condition of MaxID(Targettable)+1 and ID+1 for the subsequent unmapped record. Should I move my MaxId+1 and ID=maxId into the RC ne 0 block for that? I am not putting any test records as my actual data step has a lot of columns and data. However, If you want to have some sample data to test, feel free to comment and I will try to provide. Let's ha
... View more