10-13-2016 04:58 PM
Hi All -
I have the following columns:
MONTHS, YEAR, ACCOUNT NUMBERS and SCORE
Im using Proc sql;
I need a table that will do a group by that will give me
all the months ( as one column), amount of accounts for that months and SEPARATE the score with PASS and FAIL ( so 2 separate columns for SCORE)
thus need to sum up all the pass and/ fails and create its own columns
So I will see all the accounts for say january and all the pass and fails for those accounts in jan.
10-13-2016 06:32 PM
Is this a report or do you need a data set?
What do you mean by "amount of accounts"? Is that the number of unique accounts or total number of records or something else?
How do we know what a PASS or FAIL is? You don't have a variable or supply a rule.
It will help if you can provide some example data. Instructions here https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will create text of a data step that you can post here so we can test code.
10-14-2016 09:55 AM
create table want as
Sum(pass=0) as fail,
sum(pass=1) as pass
This is VERY similar to last question, except for pass/fail which is noted above. You should be able to combine two answers to get your desired results.
10-14-2016 10:21 AM
This is how my data looks: (sample)
year Month Account Pass/Fail
2016 2 44402 1
2016 2 99921 0
2016 4 11233 1
create table test1 as
Count(*) as count
group by year, month, pass,fail
Doesnt work tho.. am i not able to add new columns and do a group by in the same query?
10-14-2016 10:44 AM
Describe how "it doesn't work". If there are errors post the log with the code and the errors, if you get unexpected output show the output actually resulting and the desired outcome.
10-14-2016 01:47 PM
You can but your group by is the level you want to summarize on. PASS/FAIL should not be in your group by.
As indicated by someone else 'doesn't work tho' doesn't tell anyone anything about how something doesn't work.