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
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;
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;
You're correct Art, not sure what I was thinking there...
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!
yes you can do that.http://www.nesug.org/proceedings/nesug08/cc/cc19.pdf
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.