Help using Base SAS procedures

Lookup in SAS - Picking only first matched value

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Lookup in SAS - Picking only first matched value

Hello,

I am little bit fimilar with Merge in SAS and Joins in Proc SQL. May be I am missing something, but I am struggling to do a simple lookup in SAS as we do in vlookup in excel. For example

Dataset A has two variables:

ID Visits

1  Two

2  Three

1  One

3  Two

Dataset B has two variable

ID Location

1   A

2    B

3   A

1   C

2   B

All I want is my source file DatasetA should look in Dataset B, match by ID and give me the first matched Location. Dont want duplicates. Output should be:

ID Visits Location

1   Two      A

2   Three    B

1    One     A     (See location is not C but gave me the first macthed Location again)

3   Two     A

Hope thats clear.

Thanks

Sac01663


Accepted Solutions
Solution
‎01-26-2012 09:39 AM
Super Contributor
Posts: 1,636

Lookup in SAS - Picking only first matched value

How about:

data A;

input ID $ :Visits $;

cards;

1  Two

2  Three

1  One

3  Two

;

data B;

input ID $ :Location $;

cards;

1   A

2    B

3   A

1   C

2   B

;

proc sort data=b nodupkey;

by id ;

run;

proc sql;

  create table want as

     select a.id,visits,location

         from a,b

           where a.id=b.id;

quit;

proc print data=want;run;

                               Obs    ID    Visits    Location

                                  1     1     Two          A

                                  2     2     Three        B

                                  3     1     One          A

                                  4     3     Two          A

Linlin

View solution in original post


All Replies
Solution
‎01-26-2012 09:39 AM
Super Contributor
Posts: 1,636

Lookup in SAS - Picking only first matched value

How about:

data A;

input ID $ :Visits $;

cards;

1  Two

2  Three

1  One

3  Two

;

data B;

input ID $ :Location $;

cards;

1   A

2    B

3   A

1   C

2   B

;

proc sort data=b nodupkey;

by id ;

run;

proc sql;

  create table want as

     select a.id,visits,location

         from a,b

           where a.id=b.id;

quit;

proc print data=want;run;

                               Obs    ID    Visits    Location

                                  1     1     Two          A

                                  2     2     Three        B

                                  3     1     One          A

                                  4     3     Two          A

Linlin

Contributor
Posts: 40

Lookup in SAS - Picking only first matched value

Thanks Linlin,

There is way out by removing duplicates. However, I am looking for a generic solution for such kind of problems. vlookup has been very useful and solves more than half of your problems.

Basically, does anything replaces vlookup (excel) in SAS? Any procedures, joins?

Regards,

Sachin

Super Contributor
Posts: 1,636

Lookup in SAS - Picking only first matched value

Hi Sachin,

I am not familar with vlookup. Maybe someone on the Forum would be able to answer your question.

Contributor anu
Contributor
Posts: 23

Lookup in SAS - Picking only first matched value

Hi Sachin,

http://www2.sas.com/proceedings/sugi28/250-28.pdf. Check out this paper it talks about sas functions and its equivalent in excel. Hope this helps!

Super User
Posts: 17,842

Re: Lookup in SAS - Picking only first matched value

I think a format would work well for the types of lookup you're doing, but in all cases you'll need to have the lookup table de-duplicated at some point I believe.

Proc format solution to your problem involves


1. De-duplicating the data

2. Creating the format

3. Applying it to your dataset.

data have;

input id visits : $5. @@;

datalines;

1  Two 2  Three 1  One 3  Two

;

data lookup;

input id location : $1. @@;

datalines;

1   A 2   B 3   A 1   C 2   B

;

*Sort variables and rename id and location;

proc sort data=lookup out=l_format (rename=(id=start location=label));

by id location;

run;

data l_format;

    set l_format;

    *Set type of format and format name;

    retain type 'N' fmtname 'lookup';

    by start;

    if first.start; *Keep only first of each kind;

run;

*create format;

proc format cntlin=l_format;

run;

data ab;

set a;

lookup=put(id, lookup.);

run;

Occasional Contributor
Posts: 5

Re: Lookup in SAS - Picking only first matched value

it was nice answer and even i'm also faced same problem, thanks for your super answer.
Valued Guide
Posts: 765

Re: Lookup in SAS - Picking only first matched value

hi ... here's another idea ...

data a;

input id visits : $5. @@;

datalines;

1  Two 2  Three 1  One 3  Two

;

data b;

input id location : $1. @@;

datalines;

1   A 2   B 3   A 1   C 2   B

;

proc sort data=b out=b (index=(id));

by id location;

run;

data ab;

set a;

set b key=id / unique;

if _error_ then do; _error_ = 0; call missing(location); end;

run;

Regular Contributor
Posts: 171

Re: Lookup in SAS - Picking only first matched value

I believe that the answer is no, there is not a single procedure that does exactly what vlookup does.  However, as demonstrated, there are many ways to get the desired results.  Here is yet another way that does not require sorting:

data ab;

     if _n_ = 1 then do;

          if 0 then set b;

          declare hash h(dataset:'b', multidata:'y');

          h.definekey('id');

          h.definedata('location');

          h.definedone();

     end;

     set a;

     if h.find() then call missing(location);

run;

Contributor
Posts: 40

Re: Lookup in SAS - Picking only first matched value

Thanks people. All the aswers are helpful. There are many ways to get this done.

Sachin

Regular Contributor
Posts: 171

Re: Lookup in SAS - Picking only first matched value

If you really like using the Excel vlookup function, you could even create your own user-defined vlookup function with PROC FCMP.  However, you should note that of all the solutions posed, my guess is that this one will be the least efficient by far.

%macro vlookup;

     %let lookup_value = %sysfunc(dequote(&lookup_value));

     %let lookup_table = %sysfunc(dequote(&lookup_table));

     %let lookup_column = %sysfunc(dequote(&lookup_column));

     %let return_column = %sysfunc(dequote(&return_column));

     %let results = ;

     data _null_;

          if _n_=1 then set &lookup_table(keep=&lookup_column);

          type = vtype(&lookup_column);

          call symputx("type", type);

          stop;

     run;

     data _null_;

          set &lookup_table(obs = 1 keep=&lookup_column &return_column);

          %if &type = N %then where &lookup_column = &lookup_value;;

          %if &type = C %then where &lookup_column = "&lookup_value";;

          call symputx("results", &return_column);

     run;

%mend;

proc fcmp outlib=sasuser.funcs.general;

     function vlookup(lookup_value $, lookup_table $, lookup_column $, return_column $) $;

     length results $ 200;

     rc=run_macro('vlookup', lookup_value, lookup_table, lookup_column, return_column, results);

     return(results);

     endsub;

quit;

data a;

input id visits : $5. @@;

datalines;

1 Two 2 Three 1 One 3 Two

;

data b;

input id location : $1. @@;

datalines;

1 A 2 B 3 A 1 C 2 B

;

run;

options cmplib=sasuser.funcs;

data ab;

     set a;

     location=vlookup(id, 'b', 'id', 'location');

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 3263 views
  • 8 likes
  • 7 in conversation