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?
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!
If I understand your requirement then
where LenderId in ( your list of Ids)
after your From clause might work.
If I understand your requirement then
where LenderId in ( your list of Ids)
after your From clause might work.
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.
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.
It seems that I must change brackets:
count(distinct LenderID in (select LenderID from LenderIDs)) as nombLenderIDCAN
Thank you!
I have an error again. Sometimes, 1 appears when it should be 0.
I will figure out.
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.
Thank you for the reply!
I finally create a new table like you suggest.
Thank you again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.