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
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
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
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
Hi Sachin,
I am not familar with vlookup. Maybe someone on the Forum would be able to answer your question.
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!
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;
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;
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;
Thanks people. All the aswers are helpful. There are many ways to get this done.
Sachin
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: