I already answered a question like this a long while back.
Here is the code I used to solve it.
data table3 ;
input ERMNO $ _1990 _1991 _1992 _1993 _1994 ;
10002 0 0 0 0 0
10009 -98 -98 -98 -98 -98
10020 -98 -98 -98 -98 -98
10056 0 0 0 0 0
10106 -98 -98 -98 10 -98
10126 -98 -98 -98 -98 -98
10157 -98 -4.5 -98 -98 -98
10179 -2 -98 -98 -98 -98
data table4 ;
input ERMNO $ year ;
%macro GetValue( data = , VarVal = , GetVar= , NewVar= ) ;
%local rc dsnid vn val ;
%let dsnid =
%sysfunc( open ( &data (where = (upcase(ermno) = "%upcase(&VarVal)")), i ) )
%if ( &dsnid ne 0 ) %then %do ;
%let rc = %sysfunc(fetch(&dsnid,noset)) ;
%if ( &rc = 0 ) %then %do ;
%let vn = %sysfunc(varnum(&dsnid,&GetVar)) ;
%if ( &vn ne 0 ) %then %do ;
%let Val = %sysfunc(getvarn(&dsnid,&vn)) ;
%else %put %sysfunc(sysmsg()) ; /* mistake in data specs */
%else %put %sysfunc(sysmsg()) ; /* open but could not fetch */
%let rc = %sysfunc(close(&dsnid)) ;
%else %put %sysfunc(sysmsg()) ; /* could not open */
%mend GetValue ;
data Need ;
length MyNewVal 8 ;
set table4 ;
data = Need ;
When everything is coming at you all at once, your in the wrong lane.
A truly happy person is someone who can smile and enjoy the scenery on a
From: Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Reply-To: Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Subject: Re: Lookup
Date: Fri, 1 Sep 2006 08:45:25 -0400
Well, in SAS there are columns and rows or rather variables and records or
observations. While reading a dataset only one record is generally in
memory, not the whole dataset. There are ways to force the whole dataset
into memory completely (but I never used them) to speeding up the lookup,
but the most elementary way would be to specify the column by its variable
name (which it always has) and the row by the record number (a number, not
a letter). Then, with the SET option POINT you may fetch any record you
like. Tell us why and what you want to do with the lookup.
A short macro, but quite inefficient (and *untested*):
%MACRO Lookup (Data=, Variable=, Record=, Return=);
_Pointer = &Record;
SET &Data POINT=_Pointer;
CALL SYMPUT ("&Return", TRIM(LEFT(&Variable)));
* if variable is numeric automatic type conversion will take place;
* beter: find out which type (PROC SQL) and handle accordingly;
%LET Returned = ; %* create and initialize global macro variable;
Call it: %Lookup ( Data=YourData, Variable=W, Record=2, Return=Returned);
The macro variable Returned now contains the value to return after lookup.
The macro as written is very inefficient, because it has to do all of the
houskeeping at every call. It depends on what you want and what you have
as code how to could integrate this more efficiently.
If you would like to call the columns by number too you could adapt the
macro (quite drasticly) to create two arrays in the data step:
one with all numeric variables and one with all your character ones.
Then you also would have to include the variable type in the macro call
and both types would number from 1. Well, quite some work and very much
dependent on you case, so I'll leave it up to you now.
P.S. And you might have to take precautions in case the dataset is empty.
Regards - Jim
Jim Groeneveld, Netherlands
Statistician, SAS consultant
On Fri, 1 Sep 2006 02:18:27 -0700, Pravin <pravinrakshe@YAHOO.CO.IN> wrote:
>m the learner of the SAS .In one of the program i need to do lookup.I
>need to pick a value whose column & row is matching with the data set.
>A V U W X Y
>B 1 2 1 3 1
>C 2 2 1 1 3
>D 4 2 1 3 2
>so if my column matches with W & row matches with C then value should
>Hoping for the best.