topic How to perform a look-up type exercise in SAS in SAS Programming

How to perform a look-up type exercise in SAS

EC27556 — Wed, 13 Jan 2021 17:55:57 GMT

Hi, I am looking to perform a look-up type exercise but am unsure of how to carry it out.

A simplified version of my dataset is this:

  Date1         Date2         Date3       Date
11/01/2020   11/05/2020     11/30/2020    Date1
12/01/2020   08/30/2020     06/14/2020    Date3
.
.
.

What I want to do is create a new variable, say "ActualDate", that looks up the value of the variable "Date" and returns the value of the variable of that name.

So for the first row in the table above I would like ActualDate to equal "11/01/2020" and for the second row I would like it to equal "06/14/2020"

Is this possible and, if so, does anyone know what code is required? Thanks.

Re: How to perform a look-up type exercise in SAS

PhilC — Wed, 13 Jan 2021 18:51:04 GMT

Could your simplified dataset be structured like so? :

  Date1         Date2         Date3       Date
11/01/2020   11/05/2020     11/30/2020      1
12/01/2020   08/30/2020     06/14/2020      3
.
.
.

Then this becomes a solution easily solved with an array, where date, as numeric, is the array index to the date_ array.

array date_[3] date1-date3;
ActualDate=date_[date];

Re: How to perform a look-up type exercise in SAS

Tom — Wed, 13 Jan 2021 19:07:29 GMT

If you just want the formatted value of the variable (that is string) then use the VVALUEX() function.

data want;
  set have;
  want_string = vvaluex(date);
run;

Re: How to perform a look-up type exercise in SAS

EC27556 — Wed, 13 Jan 2021 19:34:47 GMT

Great, thank you! I would be able to get just the numbers in the "Date" column so this should work. Would you be able to write your solution in a generalised form though as I am having some trouble interpreting it (and therefore adjusting for my actual dataset!) I.e say Date1 was actually called X1, Date2 was X2 and Date3 was X3. Then say the "Date" variable was "Y" and "ActualDate" was "Z". How would your solution look in this case?

Many thanks for your help.