topic Re: Group by and create new column in SAS Enterprise Guide

Group by and create new column

itshere — Thu, 13 Oct 2016 20:58:56 GMT

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!

Re: Group by and create new column

Reeza — Thu, 13 Oct 2016 22:32:48 GMT

How is this different from your last question?

What do you have so far?

Re: Group by and create new column

ballardw — Thu, 13 Oct 2016 22:32:58 GMT

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-data-AKA-generate/ta-p/258712 will create text of a data step that you can post here so we can test code.

Re: Group by and create new column

itshere — Fri, 14 Oct 2016 13:50:23 GMT

total number of records for account numbers.

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

Re: Group by and create new column

Reeza — Fri, 14 Oct 2016 13:55:03 GMT

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.

Re: Group by and create new column

itshere — Fri, 14 Oct 2016 14:21:47 GMT

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?

Re: Group by and create new column

ballardw — Fri, 14 Oct 2016 14:44:33 GMT

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.

Re: Group by and create new column

Reeza — Fri, 14 Oct 2016 17:47:33 GMT

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.