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!
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.
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?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.