Help with creating a SAS table and run a query from the datawarehouse to validate

[newbeesas]

Hi all-

I am a new SAS user and have no prior knowledge or experience with SAS programming. I don't use SAS on a daily basis but trying to help my team automate a process which is manual currently. I know it can be done using SAS but I just don't know how to or where to start. I hope you can help me figure this out. We use SAS9.3.

Requirement:

I need to build a table like this below.

For ex.

Subject     Score    Grade 

Math          35            F

Math         36-50       E

Math         51-70       D

Math         71-80       C

Math         81-90       B

Math         91-100     A

Science    40             F

Science    41-50        E

Science    51-60        D

Science    61-70         C

Science    71-85        B

Science     85-100     A

Once I build the above table, I want to run a query from my datawarehouse(DW) to pull the records and validate that what the system calculated as grade is correct by using the above table. So I would need a "Calculated grade" to make sure what I see in the datawarehouse is the same as what I would calculate manually.

My question is using the score from my DW, can I calculate the grade manually (Calculated grade) and match it up against the grade generated by the system? Something like the below:

For ex.

Subject     Score    Grade  Calculated grade

Math          35            F            F

Math         36-50       E            E

Math         51-70       D            D

Math         71-80       C            C

Math         81-90       B             B

Math         91-100     A             A

Science    40             F             F

Science    41-50        E             E

Science    51-60        D             D

Science    61-70         C            B

Science    71-85        B             A

Science     85-100     A            A

I hope you can help me with the code. Thanks in advance!

[RW9]

Well, no test data (in the form of a datastep) means that this code is untested.  Also, it would make you life a lot easier if you split the range out into higher/lower:

data grades;
  length subject score grade $200;
  input subject $ score $ grade $;
datalines;
Math 35 F
...;
run;

proc sql;
  create table WANT as
  select  DW.*,
          G.GRADE as CALC_GRADE
  from    DATA_WAREHOUSE.TABLE DW
  left join GRADES G 
  on        DW.SUBJECT=G.SUBJECT
  and       input(scan(G.SCORE,1,"-"),best.) <= DW.SCORE <= COALESCE(input(scan(G.SCORE,2,"-"),best.),input(scan(G.SCORE,1,"-"),best.));
quit; 

So merge on subject, then where score is between low range and either high range if present or low range.

[newbeesas]

Thank you for your response!

What does "Also, it would make you life a lot easier if you split the range out into higher/lower:" mean?

[RW9]

SAS works on the principal that one column holds one data item, not several data items.  In your case you have two data items in one column, an upper and lower bound separated by a delimeter, which means to use each of those data items you need to split the data.  It would be better practice to have one column for lower, and one for upper.