function count -- specific data counted

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

function count -- specific data counted

Hi,

My problem is pretty basic (again) but it is very specific.

I want to count the number of lenders in each package. Each package and lender are identified by a number (PackageID and LenderID).

If my request finishes here, my code would be:

proc sql;

create table2 as

select *, count(distinct LenderID) as nombparticipant

from table1

group by PackageID

order by PackageID;

quit;

But I want to count the number of specific LenderIDs. I'm interessed in LenderID 500, 523, 1701, ... (total: 20 different LenderID).

I want to know how many of them are in each package.

How can I consider my twenty LenderIDs with the function count?

  (This is what my table looks like at the start.)

PackageID

LenderID
5678
5634
5661
5653
5613
5626
5612
7278
7235
7252
721
7284
7259
7256
7274
7246
7264
7274
7274
72100
7269
7226
7228
727

Thank you!


Accepted Solutions
Solution
‎01-15-2014 09:57 AM
Super User
Posts: 10,511

Re: function count -- specific data counted

If I understand your requirement then

where LenderId in ( your list of Ids)

after your From clause might work.

View solution in original post


All Replies
Solution
‎01-15-2014 09:57 AM
Super User
Posts: 10,511

Re: function count -- specific data counted

If I understand your requirement then

where LenderId in ( your list of Ids)

after your From clause might work.

Contributor
Posts: 62

Re: function count -- specific data counted

Thank you!

My code works, but it doesn't do the function count (i.e. I have a whole column of zeros even if it is not the right result).

I will try to figure out.

Contributor
Posts: 62

Re: function count -- specific data counted

My code is:

proc sql;

create table table2 as

select *  count(distinct LenderID) in (select LenderID from LenderIDs) as nombLenderIDCAN

from table 1

group by PackageID

order by PackageID;

quit;

Before, I have created an excel file for the twenty LenderID. I have imported to SAS. It is my table LenderIDs.

Contributor
Posts: 62

Re: function count -- specific data counted

It seems that I must change brackets:

count(distinct LenderID in (select LenderID from LenderIDs)) as nombLenderIDCAN

Thank you!

Contributor
Posts: 62

Re: function count -- specific data counted

I have an error again. Sometimes, 1 appears when it should be 0.

I will figure out.

Frequent Contributor
Posts: 83

Re: function count -- specific data counted

Ok so I think I understand what you are trying to do. I think that step 1 is that you are trying to 1.) get a distinct count of how many lenders there are to each package 2.) get a distinct count of how many lenders in a list are in a package. If this is the case code is below:

proc sql;

create table lend_in_pack as

select distinct packageid, (count(lenderid)) as numlend /*This counts the number of lenders are in each package*//*If you wanted lenders to be counted once you would add unique( after count and finish it with a )*/

from table1

group by packageid;

create table spec_lend_in_pack as

select distinct packageid, (sum(case when lenderid in (50 74 78 /*All of the other lenderids in here*/) then 1 else 0 end)) as spec_lend /*This takes the list of specific lenderids and outputs a 1 or 0 if it is in the list, then sums the list to give you your count, once again this isn't taking distinct lenderids so if one shows up 5 times, it will be counted 5 times, if you wanted specific ones, I would recommend just creating a table that only gives you distinct rows by running a select distinct * from table1 and then running this code again*/

from table1

group by packageid;

quit;

Hope this helps.

Contributor
Posts: 62

Re: function count -- specific data counted

Thank you for the reply!

I finally create a new table like you suggest.

Thank you again.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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