topic Re: Help with creating a SAS table and run a query from the datawarehouse to validate in SAS Programming

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

newbeesas — Thu, 23 Jun 2016 12:59:32 GMT

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!

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

RW9 — Thu, 23 Jun 2016 13:38:17 GMT

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.

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

newbeesas — Thu, 23 Jun 2016 19:33:26 GMT

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?

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

RW9 — Thu, 23 Jun 2016 21:45:03 GMT

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.