Need help with mapping in hash object and renaming in data step

[WorkingMan]

I have 2 tables. One is Primary table while the other one is Secondary.

I am doing a lookup with hash object.

I have some issues. Firstly, both tables are having a lot of same name column.

The issue here is that I am not familiar on how to distinguish them(i mean how to separate them in the code body as some columns from both tables are having the same name) and output them in the target with a different column name.

For example:

Table A and B have columns named Region State Station Voltage.

In the data step, I tried to rename the column Voltage to Secondary_voltage that is actually coming from Secondary by putting rename statement right after set statement. However, in my target table, secondary_voltage is totally blank/NULL while voltage in the source table is never NULL.

Secondly,

After rename, how do i still plot the hash key? Columns from both tables are having different name.

Kindly refer to my code below:

data &_OUTPUT1; 

  attrib region length=$30 label='Region';
  attrib zone length=$2 label='LF and LD Zone';
  attrib state_code length=$5 label='State Code';
  attrib state length=$30 label='State Name';
  attrib business_area_code length=$30 label='Business Area/Cost Center Code';
  attrib business_area length=$50 label='Business Area/Cost Center';
  attrib supply_zone_code length=$30 label='Supply Zone Code 33kV';
  attrib supply_zone length=$50 label='Supply Zone 33kV';
  attrib sub_supply_zone_code length=$30 label='Sub Supply Zone Code 11kV';
  attrib sub_supply_zone length=$50 label='Sub Supply Zone 11kV';
  attrib pmu_name length=$50 label='PMU Name in TNBT Web';

  if _n_=1 then do;
     declare hash pmu_list(dataset:"dm.asset_primary");
     pmu_list.definekey('pmu_name');
     pmu_list.definedata('region','zone','state_code',
                         'state','business_area_code','business_area',
                         'supply_zone_code','supply_zone',
                         'sub_supply_zone_code','sub_supply_zone',
                         'pmu_name','functional_location',
                         'substation_name_tnbt','capacity_total','capacity_firm');
     pmu_list.definedone();
     call missing(region,zone,state_code,state,business_area_code,business_area,
                  supply_zone_code,supply_zone,sub_supply_zone_code,sub_supply_zone,
                  pmu_name,functional_location,substation_name_tnbt,capacity_total,capacity_firm);
  end;
  set dm.asset_secondary;
          rename=(pmu_name=pmu_link ppu_ssu_voltage=voltage);
    pmu_name=pmu_link;
  rc=pmu_list.find();
  output &_OUTPUT1; 
run;        

I want my columns from primary target table to be renamed to primary_xxx while columns from secondary target to be renamed to secondary_xxx.

Currently I am not posting the code because I am in the train. I would appreciate if anyone can provide some logic or explanation to my problem.

I would love to post more detailed info when I am available later as I know my current post is abit short of details.

[AMSAS · Accepted solution]

Hi,

From the look of your code, you are mixing the RENAME statement with the RENAME dataset option.
The RENAME statement will rename the variables when they are written out, whereas the RENAME dataset option will rename the variable when the dataset is referenced (in this case when it is read by the SET statement).

Here's a simple example that will show the difference. In the 2nd datastep the variables in input dataset (test) are renamed (RENAME dataset option) and the new names are available in the datastep (put r1= r2= r3), whereas in the 3rd datastep the variables are renamed (RENAME statement) when the output dataset (rename2) is created.

data test ;
	infile cards ;
	input v1 $ v2 $ v3 $ ;
cards;
a b c
d e f
g h i 
;

data rename1 ;
	set test (rename=(v1=r1 v2=r2 v3=r3)) ;
	put r1= r2= r3= ;
run ;

data rename2 ;
	set test ;
	rename v1=r1 v2=r2 v3=r3 ;
	put v1= v2= v3= ;
run ;

It appears that may have been your intention to use the Dataset option, but your code has a semi-colon after the dataset name in the set statement :

  set dm.asset_secondary; /* <<< Semi colon here */
          rename=(pmu_name=pmu_link ppu_ssu_voltage=voltage);