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.
@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
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
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.
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.
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.
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;
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.
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;
@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
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.