BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JinboZhao
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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

View solution in original post

9 REPLIES 9
Reeza
Super User

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

JinboZhao
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

JinboZhao
Calcite | Level 5
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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
JinboZhao
Calcite | Level 5

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. 

BrunoMueller
SAS Super FREQ

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;
Kurt_Bremser
Super User

@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
art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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