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
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 25. Read more here about why you should contribute and what is in it for you!
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.