SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS enterprise guide function like VLOOKUP in excel

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

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.

ABCD
86A87B
87B88C
88C89D
89D90E
90E91N/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: 6,955

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

View solution in original post


All Replies
Super User
Posts: 17,868

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

Thanks for your reply.

 

My data more looks like the below:

IDTimeValueTime+1Value@Time+1
186A87B
287B88C
388C89D
489D90E
590E91N/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
Super User
Posts: 7,413

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

IDTimeValueTime+1Value@Time+1
186A87B
187B88C
188C89D
189D90E
190E91N/A
286D87E
287E88A
288A89C
289C90B
290B91N/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
Super User
Posts: 7,413

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: 683

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: 6,955

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
PROC Star
Posts: 7,363

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 179 views
  • 0 likes
  • 6 in conversation