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
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
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
Thanks for the interesting example of lookup.
Find succeeds even if the "Class variable" of "have dataset" is truncated at 8 characters.
@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 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 🙂
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.