Desktop productivity for business analysts and programmers

Group by and create new column

Reply
Contributor
Posts: 26

Group by and create new column

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!

Grand Advisor
Posts: 17,308

Re: Group by and create new column

How is this different from your last question?

What do you have so far?

Grand Advisor
Posts: 10,196

Re: Group by and create new column

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.

Contributor
Posts: 26

Re: Group by and create new column

total number of records for account numbers. 

 

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

 

Grand Advisor
Posts: 17,308

Re: Group by and create new column

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. 

Contributor
Posts: 26

Re: Group by and create new column

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?

Grand Advisor
Posts: 10,196

Re: Group by and create new column

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.

Grand Advisor
Posts: 17,308

Re: Group by and create new column

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.  

Ask a Question
Discussion stats
  • 7 replies
  • 318 views
  • 0 likes
  • 3 in conversation