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.
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.
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.