topic Re: EXCEL LOOKUP function equivalent in SAS in SAS Procedures

EXCEL LOOKUP function equivalent in SAS

J_CKY — Mon, 19 Nov 2012 16:36:26 GMT

Hi all,

not sure if this is the correct forum for the question.

I am trying to group my data set into various "scores" by the index. I know that I could probably do it in IF-THEN-ELSE logic.

But this will involve hard coding and will be hard to amend. Also, if next time I have a long list of "score" list it will be different and long winding to do it in IF-THEN-ELSE logic.

I am trying to write my codes by making use of a look up table. I know how to do it in EXCEL, but not sure how to do this in SAS.

Attached is a quick example of what I want to achieve. I want to use SAS to establish column C.

Thanks

Jack

Re: EXCEL LOOKUP function equivalent in SAS

art297 — Mon, 19 Nov 2012 16:56:50 GMT

You can use proc format to accomplish the same thing. e.g.:

proc format;

value grades

0-49="FAIL"

50-59="D"

60-69="C"

70-79="B"

80-high="A"

;

run;

data want;

set have;

grade=put(mark,grades.);

run;

Re: EXCEL LOOKUP function equivalent in SAS

FriedEgg — Mon, 19 Nov 2012 16:58:51 GMT

data tbl;

input name $ mark;

cards;

Alex 80

Bruce 76

Catherine 81

Ducan 50

Edwin 39

Felix 92

Goeff 53

Helen 67

Ivan 49

;

run;

proc format;

value mrk2gd

50 - 59 = 'D'

60 - 69 = 'C'

70 - 79 = 'B'

80 - high = 'A'

other = 'FAIL'

;

run;

data want;

set tbl;

grade=put(mark,mrk2gd.);

run;

Re: EXCEL LOOKUP function equivalent in SAS

art297 — Mon, 19 Nov 2012 17:14:19 GMT

: Using the fuzz factor that way will result in a number of incorrect classifications.

Re: EXCEL LOOKUP function equivalent in SAS

FriedEgg — Mon, 19 Nov 2012 17:16:59 GMT

You're correct Art, not sure what I was thinking there...

Re: EXCEL LOOKUP function equivalent in SAS

J_CKY — Tue, 20 Nov 2012 09:40:17 GMT

Thanks both,

Is there and way I could use a existing SAS table to PROC FORMAT, rather than hard coding in the code?

Say, for example, I already have a table called work.grades_lkup (or anything similar):

data grades_lkup;

input mark grade $;

cards;

0 FAIL

50 D

60 C

70 B

80 A

;

run;

Thanks in advance!

Re: EXCEL LOOKUP function equivalent in SAS

manojinpec — Tue, 20 Nov 2012 10:05:51 GMT

yes you can do that.http://www.nesug.org/proceedings/nesug08/cc/cc19.pdf

Re: EXCEL LOOKUP function equivalent in SAS

RichardinOz — Tue, 20 Nov 2012 11:55:45 GMT

In brief, you need a table called a SAS control table in a specified layout. Then you just invoke

proc format cntlin = your_control_table ;

quit ;

At a minimum the control table needs to have 3 columns which must be called start, label and fmtname. The first is the initial value, the second is the formatted value, and the third is the name to be assigned to the format. Values in the fmtname column are repeated for every row. You can create this table in SQL or in a datastep (imagining that the only possible marks are 0, 590, 60, 70, 80)

proc SQL ;

create table fmtcntl as

select mark as start

, grade as label

, 'gradefmt' as fmtname

from grades_lkup ;

quit ;

proc format cntlin = fmtcntl ;

quit ;

You can only have one cntlin table per proc format invocation, but the control table can have concatenated lists of lookups if each has thier own fmtname.

If you want to create a character format you have to add an additional column called type which would hold the value 'C' for each row. (The default is 'N' for a numeric format). Fir a numeric informat, type = 'I'. Other columns can hold additional information: eg end is the end value of a range. You need that if your grade format has a continuous integer range. Thus for your data (where end values have to be inferred)

data fmtcntl ;

length start end 8 grade $ 4 ;

retain fmtname 'gradefmt' ;

set grades_lkup end = lastrow ;

start = lag (mark) ;

end = mark - 1 ;

label = lag (grade) ;

if _N_ = 1

then return ;

output ;

if lastrow

then

do ;

start = mark ;

end = 100 ;

label = grade ;

output ;

end ;

drop mark ;

run ;

This may seem more effort than is worth for a simple format, as in you example, but if you lookup table contains more than a few rows it can be worthwhile especially for lookup tables which are straight transformations.

Richard in Oz