Assign values from one file to the other dataset

[lsehlola]

Hi All

I have two datasets. is a master file as seen below. The other file holds numerical data in ascending order which must be assigned to the master file depending the following condition:

Mater Data
Field_One	Field_Two	Derived_Field
Lancelot	Y	Existing Value
Sehlola	N	1
Johannesburg	N	2
Pretoria	Y	Existing Value
Randburg	N	3

Numerical Data

Field_One

1

2

3

4

5

If "Field_Two" = "Y" then "Derived_Field" = "Existing Value"

Else "Field_Two" = "N" then "Derived_Field" = "Field_One" - of the numerical dataset.

Once you have used the first record of the numerical dataset, it cannot be reused in the master file.

[PeterClemmensen]

data master;
input Field_One $ 1-12 Field_Two $ 14;
datalines;
Lancelot     Y 
Sehlola      N 
Johannesburg N 
Pretoria     Y 
Randburg     N 
;
 
data num;
input Field_One $;
datalines;
1
2
3
4
5
;

data want(drop = n k rc);

   if _N_ = 1 then do;
      dcl hash h();
      h.definekey("k");
      h.definedata("Derived_Field");
      h.definedone();

      do k = 1 by 1 until (z1);
         set num(rename=Field_One=Derived_Field) end = z1;
         h.add();
      end;
   end;

   set master;

   if Field_Two = "Y" then Derived_Field = "Existing Value";
   else do;
      n + 1;
      rc = h.find(key : n);
   end;
run;

[lsehlola]

Thank you so much. Works like a charm. Do you mind explaining the logic?

[PeterClemmensen]

Sure. In the first iteration of the data step, I declare a hash table h. I fill it with data from num using an ascending key k.

Then I read the master data using the Set Statement. If Field_Two = "Y" Then we simply set Derived_Field to "Existing Value".

If not, we increment n by one and use that value to lookup Field_Two in num. That way, we will never lookup using the same key twice.

Hope this makes sense 🙂

[Ksharp]

data master;
input Field_One $ 1-12 Field_Two $ 14;
datalines;
Lancelot     Y 
Sehlola      N 
Johannesburg N 
Pretoria     Y 
Randburg     N 
;
 
data num;
input Field_One $;
id+1;
datalines;
1
2
3
4
5
;

data want;
   if _n_ = 1 then do;
      if 0 then set num(rename=(Field_One=F));
      dcl hash h(dataset:'num(rename=(Field_One=F))',ordered:'y');
	  dcl hiter hi('h');
      h.definekey('id');
      h.definedata('id','F');
      h.definedone();
   end;
set master;
length Derived_Field $ 40;
if Field_Two='Y' then Derived_Field='Existing Value';
if Field_Two='N' then do;
 hi.first();
 Derived_Field=F ;_id=id;
 hi.next();
 h.remove(key:_id);
end;
drop F _id id;
run;