BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Quentin
Super User

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
Super User

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
Super User

@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

 

 

SASKiwi
PROC Star

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
Barite | Level 11
This code will be useful too. Thank you very much
Kurt_Bremser
Super User

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
Barite | Level 11
Thank you very for showing me how to use that function

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1909 views
  • 3 likes
  • 4 in conversation