Group by and create new column

[itshere]

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.

THANK YOU!

[Reeza]

How is this different from your last question?

What do you have so far?

[ballardw]

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.

[itshere]

total number of records for account numbers. 

pass and fail is currently in one column as 1 or 0. need to separate it. 

[Reeza]

Proc SQL;

create table want as 

select ...

Sum(pass=0) as fail,

sum(pass=1) as pass

from ...

quit;

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. 

[itshere]

This is how my data looks: (sample)

year     Month   Account    Pass/Fail

2016      2          44402      1

2016      2           99921     0

2016     4        11233         1

proc sql;
create table test1 as
select
year,
month,
sum(passfail=1)as pass,
sum(passfail=0)as fail,
Count(*) as count
From testorg
group by year, month, pass,fail
quit;

Doesnt work tho.. am i not able to add new columns and do a group by in the same query?

[ballardw]

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.

[Reeza]

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.