Lookup in SAS

Reply
Occasional Contributor
Posts: 5

Lookup in SAS

Hi,

I am a little bit familiar 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.

Super User
Posts: 5,260

Re: Lookup in SAS

I'm not familiar with VLOOKUP. Could you please describe what you want to achieve, sample input/output data etc.

Data never sleeps
Super Contributor
Posts: 644

Re: Lookup in SAS

Well, you have the basic idea for SQL joins once you straighten out SAS naming conventions (read the manuals).  SAS is rich in methods for doing lookups and joins.  There are at least 4 major ways that spring to mind.  SQL, of course (and this can be very efficient if you learn how to load a table entirely in memory); a SAS data step merge which uses a different syntax to manage a similar, but not completely identical process; using formats as a lookup table - a very powerful tool; and using a hash join - an even more powerful tool but best left until you have mastered the basics of data step processing.

My advice would be to stick to what you know  - SQL - to start with, then look at Proc Format to create formats that can be used as instant lookups.  At some point you need to become conversant with data step processing because there are some things managed much more easily that way.

Richard in NZ

Occasional Contributor
Posts: 14

Re: Lookup in SAS


data one;
input eid ename$;
datalines;
101 A
102 B
103 C
104 D
105 C
;
run;

data two;
input eid dept$;
datalines;
102 Mar
104 ACC
105 Pha
;
run;

proc sort data=one;
by eid;
run;

proc sort data=two;
by eid;
run;

/* In data step */
data res;
merge one(in=x) two(in=y);
by eid;
if x=1;
run;

/* In SQL */
proc sql noprint;
create table res as
(select a.eid,a.ename,b.dept from one as a left join two as b on a.eid=b.eid);
quit;

Super User
Super User
Posts: 6,502

Re: Lookup in SAS

Use a format.  For many usages there will be no need to store the decode (lookup) as a new variable.

proc format ;

value sex 1='Male' 2='Female';

run;

data have ;

  input name $ sex ;

cards;

Alfred 1

Jane 2

run;


proc print; run;


Obs     name     sex

1     Alfred     1

2     Jane       2


proc print;

  format sex sex.;

run;

Obs     name      sex

1     Alfred    Male

2     Jane      Female

If you want to create a new variable use the PUT() function.

data want ;

  set have ;

  gender= put(sex,sex.);

run;

proc print; run;

Obs     name     sex    gender

1     Alfred     1     Male

2     Jane       2     Female

Occasional Contributor
Posts: 5

Re: Lookup in SAS

Hi Tom,

Thanks for your information.

Thanks & Regards,

Nag

Valued Guide
Posts: 3,208

Re: Lookup in SAS

VLOOKUP: What it is, and when to use it - Excel - Office.com

The one that does the work to be as closest to this is the Format approach as described by tom

Although the format usage is more associated as using the cell properties to change the diplay lay-out is also very smart to use in transformations.

It is possible to create format form SAS-datasets Base SAS(R) 9.3 Procedures Guide, Second Edition   

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 287 views
  • 0 likes
  • 6 in conversation