topic Re: function count -- specific data counted in SAS Programming

function count -- specific data counted

windyboo — Wed, 15 Jan 2014 14:09:36 GMT

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
56	78
56	34
56	61
56	53
56	13
56	26
56	12
72	78
72	35
72	52
72	1
72	84
72	59
72	56
72	74
72	46
72	64
72	74
72	74
72	100
72	69
72	26
72	28
72	7
…	…

Thank you!

Re: function count -- specific data counted

ballardw — Wed, 15 Jan 2014 14:57:47 GMT

If I understand your requirement then

where LenderId in ( your list of Ids)

after your From clause might work.

Re: function count -- specific data counted

windyboo — Wed, 15 Jan 2014 15:58:09 GMT

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.

Re: function count -- specific data counted

windyboo — Wed, 15 Jan 2014 16:01:47 GMT

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.

Re: function count -- specific data counted

windyboo — Wed, 15 Jan 2014 16:09:05 GMT

It seems that I must change brackets:

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

Thank you!

Re: function count -- specific data counted

windyboo — Wed, 15 Jan 2014 16:22:31 GMT

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

I will figure out.

Re: function count -- specific data counted

overmar — Wed, 15 Jan 2014 18:16:32 GMT

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.

Re: function count -- specific data counted

windyboo — Fri, 17 Jan 2014 19:52:13 GMT

Thank you for the reply!

I finally create a new table like you suggest.

Thank you again.