topic Re: is there a way to use the lookupvalue function to put the selected value in a macro variable in SAS Enterprise Guide

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

alepage — Thu, 23 Feb 2023 21:50:00 GMT

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;

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

Quentin — Thu, 23 Feb 2023 21:57:00 GMT

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.

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

Kurt_Bremser — Thu, 23 Feb 2023 22:18:41 GMT

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)?

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

Quentin — Thu, 23 Feb 2023 22:28:34 GMT

@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

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

SASKiwi — Fri, 24 Feb 2023 00:07:57 GMT

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;

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

Kurt_Bremser — Fri, 24 Feb 2023 05:42:58 GMT

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

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

Kurt_Bremser — Fri, 24 Feb 2023 05:52:07 GMT

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.

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

alepage — Fri, 24 Feb 2023 13:15:42 GMT

This code will be useful too. Thank you very much

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

alepage — Fri, 24 Feb 2023 13:17:48 GMT

Thank you very for showing me how to use that function