Solved
Contributor
Posts: 34

# SAS enterprise guide function like VLOOKUP in excel

Hi,I encounter a problem in SAS enterprise guide. I would like to find a function like VLOOKUP in excel, could anyone kindly help me about this?

For example, I have a table looks like below. I already have column A, B. Column C=Column A+1. I want to add a column D, which values in Column D = value in Column B, if and when Column C value equals to Column A.

 A B C D 86 A 87 B 87 B 88 C 88 C 89 D 89 D 90 E 90 E 91 N/A

Could anyone kindly show me how to do this in Query Builder, or show me which function I can use to get this result?

Because I have millions of data, I could not do it in spreedsheet.

Thanks million.

Accepted Solutions
Solution
‎06-21-2017 04:04 AM
Super User
Posts: 9,934

## Re: SAS enterprise guide function like VLOOKUP in excel

JinboZhao wrote:
 ID Time Value Time+1 Value@Time+1 1 86 A 87 B 1 87 B 88 C 1 88 C 89 D 1 89 D 90 E 1 90 E 91 N/A 2 86 D 87 E 2 87 E 88 A 2 88 A 89 C 2 89 C 90 B 2 90 B 91 N/A

Sorry, this is exactly what my data looks like. Could you show me the code to get the Value at Time+1?

Many appreciation.

Use a reverse sort and the lag() function:

data have;
input id time value \$;
datalines;
1 86 A
1 87 B
1 88 C
1 89 D
1 90 E
2 86 D
2 87 E
2 88 A
2 89 C
2 90 B
;
run;

proc sort data=have;
by id descending time;
run;

data want;
set have;
by id;
time_plus_1 = time + 1;
lag_time = lag(time);
lag_value = lag(value);
if first.id then value_plus_1 = 'N/A';
else if time_plus_1 = lag_time then value_plus_1 = lag_value;
else value_plus_1 = 'N/A';
drop lag_value lag_time;
run;

proc sort data=want;
by id time;
run;

proc print data=want noobs;
run;

Result:

time_    value
id    time    value    plus_1    plus_

1     86      A         87       B
1     87      B         88       C
1     88      C         89       D
1     89      D         90       E
1     90      E         91       N/A
2     86      D         87       E
2     87      E         88       A
2     88      A         89       C
2     89      C         90       B
2     90      B         91       N/A
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 23,357

## Re: SAS enterprise guide function like VLOOKUP in excel

Your description looks like a computer column rather than a look up.

Here's some basic instructions on how that may look

https://support.sas.com/documentation/onlinedoc/guide/tut42/en/m9_1.htm

Contributor
Posts: 34

## Re: SAS enterprise guide function like VLOOKUP in excel

My data more looks like the below:

 ID Time Value Time+1 Value@Time+1 1 86 A 87 B 2 87 B 88 C 3 88 C 89 D 4 89 D 90 E 5 90 E 91 N/A

I have ID, Time, Value. I would like to know the Value at Time+1. It will be a new column, could you kindly show me the function I should use in SAS Enterprise Guide? Or some code?

Many appreciation.

Super User
Posts: 9,444

## Re: SAS enterprise guide function like VLOOKUP in excel

To do this type of thing in SAS you would use merging/joining techniques:

proc sql;
create table WANT as
select  MAIN.*,
EXTRA.B as D
from    HAVE MAIN
left join HAVE EXTRA
on        MAIN.C=EXTRA.A;
quit;

However if your data has multiples, maybe there are two 88's, then you will not get the result you want.  Also, not a great idea to call variables A and B etc.

Contributor
Posts: 34

## Re: SAS enterprise guide function like VLOOKUP in excel

 ID Time Value Time+1 Value@Time+1 1 86 A 87 B 1 87 B 88 C 1 88 C 89 D 1 89 D 90 E 1 90 E 91 N/A 2 86 D 87 E 2 87 E 88 A 2 88 A 89 C 2 89 C 90 B 2 90 B 91 N/A

Sorry, this is exactly what my data looks like. Could you show me the code to get the Value at Time+1?

Many appreciation.

Super User
Posts: 9,444

## Re: SAS enterprise guide function like VLOOKUP in excel

Note how I put the test data, please use in future.  This should work:

data have;
input id time value \$;
datalines;
1	86	A
1	87	B
1	88	C
1	89	D
1	90	E
2	86	D
2	87	E
2	88	A
2	89	C
2	90	B
;
run;

proc sql;
create table WANT as
select  HAVE1.*,
case when HAVE2.TIME is null then HAVE1.TIME+1 else HAVE2.TIME end as TIME_1,
case when HAVE2.VALUE is null then "NA" else HAVE2.VALUE end as VALUE_1
from    HAVE HAVE1
left join HAVE HAVE2
on      HAVE1.TIME=HAVE2.TIME-1
order by HAVE1.ID,HAVE1.TIME;
quit;
Contributor
Posts: 34

## Re: SAS enterprise guide function like VLOOKUP in excel

Sorry, I got the following result based on the test data.

. . "2 86 D" . "1 88 C"
. . "1 88 C" . "1 88 C"
. . "2 86 D" . "2 89 C"
. . "2 89 C" . "2 86 D"
. . "2 86 D" . "2 86 D"
. . "2 89 C" . "2 89 C"
. . "1 88 C" . "2 89 C"
. . "2 89 C" . "1 88 C"
. . "1 88 C" . "2 86 D"

Is there any function in SAS Enterprise Guide?

I am a newer in SAS coding, sorry I could not get it.

SAS Super FREQ
Posts: 810

## Re: SAS enterprise guide function like VLOOKUP in excel

Some code change make it work, data values in the DATA Step where delimited by a tab char changed to comma.

Add another join condition for the id.

data have;
infile cards dlm=",";
input id time value \$;
datalines;
1,86,A
1,87,B
1,88,C
1,89,D
1,90,E
2,86,D
2,87,E
2,88,A
2,89,C
2,90,B
;
run;

proc sql;
create table want as
select
have1.*
, case
when have2.time is null then have1.time+1
else have2.time
end as time_1
, case
when have2.value is null then "n/a"
else have2.value
end as value_1
from
have have1
left join
have have2
on
have1.id = have2.id
and have1.time=have2.time-1
order by
have1.id
,have1.time
;
quit;
Solution
‎06-21-2017 04:04 AM
Super User
Posts: 9,934

## Re: SAS enterprise guide function like VLOOKUP in excel

JinboZhao wrote:
 ID Time Value Time+1 Value@Time+1 1 86 A 87 B 1 87 B 88 C 1 88 C 89 D 1 89 D 90 E 1 90 E 91 N/A 2 86 D 87 E 2 87 E 88 A 2 88 A 89 C 2 89 C 90 B 2 90 B 91 N/A

Sorry, this is exactly what my data looks like. Could you show me the code to get the Value at Time+1?

Many appreciation.

Use a reverse sort and the lag() function:

data have;
input id time value \$;
datalines;
1 86 A
1 87 B
1 88 C
1 89 D
1 90 E
2 86 D
2 87 E
2 88 A
2 89 C
2 90 B
;
run;

proc sort data=have;
by id descending time;
run;

data want;
set have;
by id;
time_plus_1 = time + 1;
lag_time = lag(time);
lag_value = lag(value);
if first.id then value_plus_1 = 'N/A';
else if time_plus_1 = lag_time then value_plus_1 = lag_value;
else value_plus_1 = 'N/A';
drop lag_value lag_time;
run;

proc sort data=want;
by id time;
run;

proc print data=want noobs;
run;

Result:

time_    value
id    time    value    plus_1    plus_

1     86      A         87       B
1     87      B         88       C
1     88      C         89       D
1     89      D         90       E
1     90      E         91       N/A
2     86      D         87       E
2     87      E         88       A
2     88      A         89       C
2     89      C         90       B
2     90      B         91       N/A
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 8,150

## Re: SAS enterprise guide function like VLOOKUP in excel

I think a simple datastep merge does what you want:

data have;
infile cards dlm='09'x;
input id time value \$ timeplus1;
datalines;
1	86	A	87
1	87	B	88
1	88	C	89
1	89	D	90
1	90	E	91
2	86	D	87
2	87	E	88
2	88	A	89
2	89	C	90
2	90	B	91
;
run;

data want;
length value_at_plus1 \$3;
merge have (in=ina) have (in=inb keep=id time value
rename=(time=timeplus1 value=value_at_plus1));
by id timeplus1;
if ina;
if not(inb) then value_at_plus1='N/A';
run;

Art, CEO, AnalystFinder.com

☑ This topic is solved.