Replace blanks in column using lookup table - don't want to use leftjoin

[mattbnorris]

Hello,

Basic version of my data is shown in the table below, the blank Location is due to prior uploads into this dataset not including the Location field.

Location	Class	Value	Period
	006_Product	35345	202207
	007_Product	25346	202208
	008_Product	3255	202209
AHT	009_Product	67777	202210
AHT	009_Product	251581	202210

I also have this mapping table available:

Class	Location
006_Product	GLP
007_Product	AHT2
008_Product	GLP
009_Product	AHT
010_Product	AHT

I would like to know if there is a way of doing something like if Location = "" then use the mapping table to lookup the product code in Class and set the Location to the correct value based on the mapping table. I know I could do a join but from previous experiences using this function it would append a new Location column the dataset; I want to keep the columns in the same order and also not overwrite the Locations that are already in the current dataset.

Any help is appreciated 🙂

Regards,

Matt

[PeterClemmensen · Accepted solution]

Try this

data have;
input Location $ Class $ Value Period;
infile datalines dlm = '|';
datalines;
   |006_Product|35345|202207  
   |007_Product|25346|202208  
   |008_Product|3255 |202209  
AHT|009_Product|67777|202210  
AHT|009_Product|251581|202210 
;

data map;
input Class :$11. Location $;
datalines;
006_Product GLP  
007_Product AHT2 
008_Product GLP  
009_Product AHT  
010_Product AHT  
;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'map');
      h.definekey('Class');
      h.definedata('Location');
      h.definedone();
   end;

   set have;

   if Location = '' then h.find();
run;

Result:

Location  Class     Value   Period
GLP       006_Prod  35345   202207
AHT2      007_Prod  25346   202208
GLP       008_Prod  3255    202209
AHT       009_Prod  67777   202210
AHT       009_Prod  251581  202210

[kimiko]

Thanks for the interesting example of lookup.

Find succeeds even if the "Class variable" of "have dataset" is truncated at 8 characters.

[PeterClemmensen]

@kimiko sure thing.

I just edited my code with sufficient lengths for Class in both data sets. It succeeds by 'luck' only because the hash object inherits variable lengths from the pdv (parameter type matching). 

[HumbleMe]

hey, perter,
why you guys always use "Have" or "Want" as the data set name? Is there any implications for those names?

[PeterClemmensen]

@HumbleMe welcome to the SAS Communities. 

Good question. But no. None other than that the 'Have' data set represents the data that we Have and the 'Want' data set represents the data set / result that we want 🙂

[HumbleMe]

Alright, that's what the words exactly stand for 😄 thanks Peter.

[Reeza]

It doesn't have to add a new column. You can use coalesce to only replace values that are not missing.

proc sql;
create table want as
select coalesce(t1.location, t2.location) as location, t1.class, t1.value, t1.period
from have as t1
left join t2
on t1.class=t2.class
order by t1.period;
quit;

You could also create a format from table2 and apply it via a data step.

data lookup_fmt;
set table2;
fmtname = 'location_fmt';
type = 'C';
start = class;
label = location;
run;

proc format cntlin=lookup_fmt;
run;

data want;
set have;
if missing(location) then location = put(class, $location_fmt.);
run;

---

@mattbnorris wrote:

Hello,

Basic version of my data is shown in the table below, the blank Location is due to prior uploads into this dataset not including the Location field.

Location	Class	Value	Period
	006_Product	35345	202207
	007_Product	25346	202208
	008_Product	3255	202209
AHT	009_Product	67777	202210
AHT	009_Product	251581	202210

I also have this mapping table available:

Class	Location
006_Product	GLP
007_Product	AHT2
008_Product	GLP
009_Product	AHT
010_Product	AHT

I would like to know if there is a way of doing something like if Location = "" then use the mapping table to lookup the product code in Class and set the Location to the correct value based on the mapping table. I know I could do a join but from previous experiences using this function it would append a new Location column the dataset; I want to keep the columns in the same order and also not overwrite the Locations that are already in the current dataset.

Any help is appreciated 🙂

Regards,

Matt

---