Help using Base SAS procedures

EXCEL LOOKUP function equivalent in SAS

Reply
Contributor
Posts: 22

EXCEL LOOKUP function equivalent in SAS

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

PROC Star
Posts: 7,474

Re: EXCEL LOOKUP function equivalent in SAS

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;

Trusted Advisor
Posts: 1,301

Re: EXCEL LOOKUP function equivalent in SAS

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;

PROC Star
Posts: 7,474

Re: EXCEL LOOKUP function equivalent in SAS

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

Trusted Advisor
Posts: 1,301

Re: EXCEL LOOKUP function equivalent in SAS

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

Contributor
Posts: 22

Re: EXCEL LOOKUP function equivalent in SAS

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!

Frequent Contributor
Posts: 139

Re: EXCEL LOOKUP function equivalent in SAS

Super Contributor
Posts: 644

Re: EXCEL LOOKUP function equivalent in SAS

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

Ask a Question
Discussion stats
  • 7 replies
  • 551 views
  • 8 likes
  • 5 in conversation