Help using Base SAS procedures

Can I count non-numeric field in proc SQL ?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Can I count non-numeric field in proc SQL ?

I have insurance claim database. I want to know from which hospital I got maximum claims. How can I do this in PROC SQL procedure?

Thanks in advance.

Arun


Accepted Solutions
Solution
‎03-27-2012 09:33 AM
Respected Advisor
Posts: 3,124

Can I count non-numeric field in proc SQL ?

Linlin,

Count(*) will count all of the rows. In the cases when only populated fields need be counted, the variable names will be needed:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

;

proc sql ;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

 

  quit;

Haikuo

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Can I count non-numeric field in proc SQL ?

Can you post some sample/fake data that we can have a taste of it?

Occasional Contributor
Posts: 9

Can I count non-numeric field in proc SQL ?

Here is sample data for Claim table:

BenefUserIDClmAmountApproveAmountStatusNameTreatmentNameHospId
1082013317121SettledSurgical ManagementHopID-846
1143700028080SettledCataractHopID-490
1174000040000SettledSurgical ManagementHopID-32
141140780CancelledCataractHopID-766
1471683715637SettledSurgical ManagementHopID-490
1481205311953SettledMedical Management ( Conservartive Management)HopID-846
1595000020000SettledSurgical ManagementHopID-499
1862123116000SettledMedical Management ( Conservartive Management)HopID-490

  1. Which hospital has got more number of patients?
Respected Advisor
Posts: 3,124

Can I count non-numeric field in proc SQL ?

If one row is equivalent to one patient, then linlin's solution is good for you. if you only care the number of different patient, the use: count (distinct BenefUserID)

Regards,

Haikuo

Super Contributor
Posts: 1,636

Re: Can I count non-numeric field in proc SQL ?

do you want something like below?

data have;

input hospital $ claim;

cards;

a 23

b 45

a 56

b 43

a 55

;

proc sql outobs=1;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

        

  quit;

updated after Doc Muhlbaier and Haikuo's comments.  Thank you!

Trusted Advisor
Posts: 2,113

Can I count non-numeric field in proc SQL ?

Linlin -- slick!  Learned something new; good way to start the day.

One correction, no parentheses around the OUTOBS=1 option.

Solution
‎03-27-2012 09:33 AM
Respected Advisor
Posts: 3,124

Can I count non-numeric field in proc SQL ?

Linlin,

Count(*) will count all of the rows. In the cases when only populated fields need be counted, the variable names will be needed:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

;

proc sql ;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

 

  quit;

Haikuo

Frequent Contributor
Frequent Contributor
Posts: 94

Can I count non-numeric field in proc SQL ?

A nice way of limiting the output - looks like it'll be a handy workaround for the lack of "top" in SAS's SQL implementation.

One thing worth noting though is that in the event of a tie it would only print out the first.  For example if two hopitals both had 50 claims, only the first would be output - presumably this is determined by input data order, but I guess we'd have to take it as arbitrary.

PROC Star
Posts: 7,357

Can I count non-numeric field in proc SQL ?

DF: While I, too, learned something new from Linlin's post, I agree that neither of the suggestions address ties.  How about:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

c 55

;

proc sql ;

  select hospital from

    (select hospital,count(claim) as counts

       from have

         group by hospital)

           having counts=max(counts)

   ;

quit;

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 1275 views
  • 6 likes
  • 6 in conversation