BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
windyboo
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

If I understand your requirement then

where LenderId in ( your list of Ids)

after your From clause might work.

View solution in original post

7 REPLIES 7
ballardw
Super User

If I understand your requirement then

where LenderId in ( your list of Ids)

after your From clause might work.

windyboo
Calcite | Level 5

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.

windyboo
Calcite | Level 5

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.

windyboo
Calcite | Level 5

It seems that I must change brackets:

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

Thank you!

windyboo
Calcite | Level 5

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

I will figure out.

overmar
Obsidian | Level 7

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.

windyboo
Calcite | Level 5

Thank you for the reply!

I finally create a new table like you suggest.

Thank you again.

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!

How to Concatenate Values

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.

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
  • 629 views
  • 0 likes
  • 3 in conversation