BookmarkSubscribeRSS Feed
J_CKY
Obsidian | Level 7

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

7 REPLIES 7
art297
Opal | Level 21

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;

FriedEgg
SAS Employee

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;

art297
Opal | Level 21

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

FriedEgg
SAS Employee

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

J_CKY
Obsidian | Level 7

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!

RichardinOz
Quartz | Level 8

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1760 views
  • 8 likes
  • 5 in conversation