turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Help with creating a SAS table and run a query fro...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2016 08:59 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to newbeesas

06-23-2016 09:38 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2016 03:33 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to newbeesas

06-23-2016 05:45 PM

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.