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

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

mattbnorris — Tue, 15 Nov 2022 18:48:18 GMT

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

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

PeterClemmensen — Tue, 15 Nov 2022 21:05:31 GMT

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

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

Reeza — Tue, 15 Nov 2022 21:18:13 GMT

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

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

kimiko — Wed, 16 Nov 2022 05:48:05 GMT

Thanks for the interesting example of lookup.

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

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

PeterClemmensen — Wed, 16 Nov 2022 07:58:59 GMT

@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).

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

HumbleMe — Wed, 16 Nov 2022 09:06:30 GMT

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

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

PeterClemmensen — Wed, 16 Nov 2022 09:08:28 GMT

@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 🙂

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

HumbleMe — Wed, 16 Nov 2022 09:22:44 GMT

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