BookmarkSubscribeRSS Feed
itshere
Obsidian | Level 7

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!

7 REPLIES 7
Reeza
Super User

How is this different from your last question?

What do you have so far?

ballardw
Super User

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
Obsidian | Level 7

total number of records for account numbers. 

 

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

 

Reeza
Super User

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
Obsidian | Level 7

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
Super User

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
Super User

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1839 views
  • 0 likes
  • 3 in conversation