turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- SAS enterprise guide function like VLOOKUP in exce...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-20-2017 09:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JinboZhao

06-20-2017 10:44 AM

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

Maxims of Maximally Efficient SAS Programmers

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JinboZhao

06-20-2017 09:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

06-20-2017 10:03 AM

Thanks for your reply.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JinboZhao

06-20-2017 09:59 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-20-2017 10:11 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JinboZhao

06-20-2017 10:17 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-20-2017 10:27 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-20-2017 10:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JinboZhao

06-20-2017 10:44 AM

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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JinboZhao

06-20-2017 12:34 PM

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