topic Create a variable based on another dataset in SAS Programming

Create a variable based on another dataset

Melk — Fri, 02 Jun 2017 00:18:02 GMT

I have 2 datasets, call them data1 and data2. They both have the same set of variables, one of them being ID.

data2 is a subset of data1. I want to create a variable in data1 that takes on the value of 1 when the ID variable is present in data2, and 0 if not in data2.

What is the most efficient way to do this?

Re: Create a variable based on another dataset

Shmuel — Fri, 02 Jun 2017 00:32:18 GMT

If needed sort datasets by ID then use merge:

data want;
merge data1(in=in1)
      data2(in=in2 keep=ID)
 ;  by ID;
       if in1 and in2 then flag=1;
       else flag=2;
run;

Re: Create a variable based on another dataset

ChrisNZ — Fri, 02 Jun 2017 03:08:01 GMT

Efficient means no sorting.

Like this ?

data WANT; 
  if _N_ = 1 then do;  
    declare hash VET(dataset: "DATA2"); 
    VET.definekey("KEYVAR"); 
    VET.definedone();
  end; 
  set DATA1; 
  FLAG=VAT.check();
run;

Re: Create a variable based on another dataset

andreas_lds — Fri, 02 Jun 2017 07:43:03 GMT

The function check returns 0 if the key was found in the hash object.

FLAG = (VAT.check() = 0);

Re: Create a variable based on another dataset

ChrisNZ — Sat, 03 Jun 2017 10:09:35 GMT

True 🙂

Re: Create a variable based on another dataset

Melk — Tue, 06 Jun 2017 23:17:55 GMT

Hello - thanks for the response. I tried this and the merged dataset has duplicate observations. Is there a way to just add a variable, flag, to data1, with value 1 if the ID variable is in data2, an value 0 if not? (without doing a merge)?

Re: Create a variable based on another dataset

Melk — Tue, 06 Jun 2017 23:22:34 GMT

Thank you both! worked perfectly with the edit from andreas.