How do i assign new Id+1 for un-matched lookup record?

[imdickson]

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

[PeterClemmensen · Accepted solution]

Always hard to answer a question like this without data so we can run the code and test, but here goes. 

Yes, if you want to assign the ID value from the calculated MaxID, you should move that logic into the do group (rc is not equal to zero if no match is found in the hash object).

However, I believe you should use your macro variable &MaxID. in this calculation instead of a simple data step variable?

As a side note, you do not need two ATTRIB Statements. You can just do

attrib ReCapacity length=8
       Id length=8;