topic Re: SAS enterprise guide function like VLOOKUP in excel in SAS Data Management

SAS enterprise guide function like VLOOKUP in excel

JinboZhao — Tue, 20 Jun 2017 13:39:35 GMT

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.

Re: SAS enterprise guide function like VLOOKUP in excel

Reeza — Tue, 20 Jun 2017 13:57:56 GMT

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

Re: SAS enterprise guide function like VLOOKUP in excel

RW9 — Tue, 20 Jun 2017 13:59:03 GMT

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.

Re: SAS enterprise guide function like VLOOKUP in excel

JinboZhao — Tue, 20 Jun 2017 14:03:32 GMT

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.

Re: SAS enterprise guide function like VLOOKUP in excel

JinboZhao — Tue, 20 Jun 2017 14:11:12 GMT

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.

Re: SAS enterprise guide function like VLOOKUP in excel

RW9 — Tue, 20 Jun 2017 14:17:04 GMT

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;

Re: SAS enterprise guide function like VLOOKUP in excel

JinboZhao — Tue, 20 Jun 2017 14:27:44 GMT

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.

Re: SAS enterprise guide function like VLOOKUP in excel

Kurt_Bremser — Tue, 20 Jun 2017 14:44:40 GMT

@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

Re: SAS enterprise guide function like VLOOKUP in excel

BrunoMueller — Tue, 20 Jun 2017 14:50:46 GMT

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;

Re: SAS enterprise guide function like VLOOKUP in excel

art297 — Tue, 20 Jun 2017 16:34:37 GMT

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