is there a way to use the lookupvalue function to put the selected value in a macro variable

[alepage]

Hello,

I just saw on the web the lookupvalue function.  Is there a way to provide a company name and to put the corresponding codeid into a macro variable ?

What's the best way to do that ?

data DirInfo1;
   input codeid $ name $ ;
   datalines;
1A2345 IA
1B2354 Promutuel
1C2366 Desjardins
1D2378 TD
run;
data _null_;
if name eq IA then %let codeid=lookupvalue('DirInfo1', IA)
run;

[Quentin]

I've never seen the LookUpValue() function.  Looks like that is a SAS Intelligent Decisioning thing.  Are you using that product?

Are you familiar with using SAS formats for lookups?  That would be the usual BASE SAS approach.  And you could use the format to generate a macro variable with the company name.

[Kurt_Bremser]

Your code is not valid in several senses.

Macro statements like %LET are executed while code is fetched, before the DATA step is even compiled. Since then the macro code resolves to nothing for the data step compiler, it gets this:

data _null_;
if name eq IA then

(because of a missing semicolon, the run becomes part of the %LET)

If you want to create macro variables from data step values, you must use CALL SYMPUT or CALL SYMPUTX.

A search for lookupvalue on documentation.sas.com gets zero results, so where did you find that (provide a link)?

[Quentin]

---

@Kurt_Bremser wrote:

A search for lookupvalue on documentation.sas.com gets zero results, so where did you find that (provide a link)?

---

Lookupvalue is not a SAS language function, it's apparently a function in the SAS Intelligent Decisioning product (solution?):

https://documentation.sas.com/doc/en/edmcdc/5.4/edmug/n0tamns22magmun1fhnw8mu61k4i.htm

I guess it's a Viya product/solution?

https://www.sas.com/en_us/software/intelligent-decisioning.html

[Kurt_Bremser]

But this means it is part of that product's specific "language" and cannot be used in a SAS Foundation DATA step.

[SASKiwi]

Here's how a lookup format can work:

data DirInfo1;
   input label $ start : $12. ;
fmtname = '$codeid';
   datalines;
1A2345 IA
1B2354 Promutuel
1C2366 Desjardins
1D2378 TD
run;

proc format cntlin = DirInfo1;
run;

data _null_;
  name = 'TD';
  codeid = put(name, $codeid.);
  put _all_;
run;

%let name = TD;
%let codeid = %sysfunc(putc(&name, $codeid.));
%put codeid = &codeid;

[alepage]

This code will be useful too. Thank you very much

[Kurt_Bremser · Accepted solution]

After @Quentin 's post, I think this can do what you want:

data DirInfo1;
input codeid $ name $ ;
datalines;
1A2345 IA
1B2354 Promutuel
1C2366 Desjardins
1D2378 TD
;
data want;
set have;
if _n_ = 1
then do;
  length codeid $8;
  declare hash lookup (dataset:"dirinfo1");
  lookup.definekey("name");
  lookup.definedata("codeid");
  lookup.definedone();
end;
if lookup.find() ne 0 then codeid = "";
run;

The "have" dataset has the variable name which you want to find the codeid for. "have" should not already have a variable codeid.

[alepage]

Thank you very for showing me how to use that function