BookmarkSubscribeRSS Feed
SAS_new
Calcite | Level 5

Hello-

I have typically used the first last statement for unique counting.  I wanted to ask if there was a way to use the first last statemetn for duplicate counting as well?  For example, patientA visited clinic1 clinic2 clinic3 clinic1 clinic4 clinic1 (for a total visit of 6 visits for patientA) in month1 and PatientB visitted clinic2 clinic3 clinic1 clinic2 clinic2 (total 5 visits) in month1 for patientB and PatientC visitted clinic1 clinic3 clinic1 clinic2 clinic2 (total 5 visits) in month1 for patientC.  I want to know the number of visits by month.

So I would have in month1 column

5visits=2 patients

6 visits=1 patient. 

Can I use first last or is there an alternative way to approach this?


18 REPLIES 18
Haikuo
Onyx | Level 15

How does your raw data/ wanted data look like?

SAS_new
Calcite | Level 5

Hi-

Data looks like this but at a more grander scale.   The dollar amounts are co-pays but Im not interested in that.  I want to know how many patients made 5 visits to clinics or 6 visits to clinics etc....

Month= January

patientclinic1clinic2clinic3clinic4
A$65
A $125
A $458
A$760
A $1,025
A$2,065
B $456
B $598
B$1,095
B $658
B $354
C$875
C 358
C510
C 450
C 265
Astounding
PROC Star

You could use first. and last. variables, if your data were sorted by MONTH PATIENT.  It wouldn't be a bad exercise to try that.  But another important tool to learn is that PROC FREQ is built to count, and can create output data sets.  This report won't be pretty, but it will have the information in it:

proc freq data=have;

   tables month * patient / noprint out=counts;

run;

proc freq data=counts;

   tables month * count;

   label count='Patients with this Number of Visits';

run;

Good luck.


ballardw
Super User

To add to Astounding's idea, if you don't have the month but have a datevalue associate a format such as MONYY. to do the grouping without having to add additional varialbes.

If you data set is of long enough time you probably want include the year also and the above will take care of it.

SAS_new
Calcite | Level 5

Yes I have considered proc freq but i am interested in the number of patients making the total number of clinic visits per month.

In my data example above I would want to know

there were 2 patients visited the clinic 5 times in January and 1 patient visiting 6 clinics in January.

5 clinic visits= 2 patients

6 clinic visits=1 patient

Astounding
PROC Star

Yes, that's what the second PROC FREQ gives you.  If you actually have a separate file for each month, but don't have a variable called MONTH, the program becomes easier:

proc freq data=january;

   tables patient / noprint out=counts;

run;

proc freq data=counts;

   tables count;

   title 'Number of Patients with N Visits in January';

run;

Good luck.

SAS_new
Calcite | Level 5

Ohh double wow!  Thanks Astounding- thats an even easier approach.   No separate file for each month- so how would I produce the counts by month using the proc freq procedure?


SteveNZ
Obsidian | Level 7

Wouldn't this work as well?

proc summary noprint nway n

    data = visits ;

            class patient ;

    output out = want (

                    drop = _type_

                    rename = (_freq_ = visits)) ;

run ;

SteveNZ
Obsidian | Level 7

Could add month easy as well

proc summary noprint nway n

    data = visits ;

            class month patient ;

    output out = want (

                    drop = _type_

                    rename = (_freq_ = visits)) ;

run ;

SAS_new
Calcite | Level 5

Thank you for responding.  This one isnt quite what I had in mind.  I did not need each patient displayed.  I need the number of patients and the number of clinics visited each month.   So below would read- two patients made 5 clinic visits and 1 patient made 6 clinic visits.

number of visits          number of patients

5                              2

6                              1


Astounding
PROC Star

To include MONTH as a variable, just use the first posted solution.  You might want to play with the format of the output from the second PROC FREQ, such as:

tables month * count / LIST;

Steve, that's just my bias.  I prefer to use PROC FREQ to count and PROC SUMMARY to generate statistics.  But there is an area where their capabilities overlap.  At any rate, you would need a second summarization of your output data set to count how many patients had 5 visits, how many had 6 visits, etc.

SAS_new
Calcite | Level 5

Astounding-  I just realized that the frequency unduplicated the counts (counted a clinic only once).  I need every clinic counted for each patient.   Do you think I can still use the frequency procedure? .

PGStats
Opal | Level 21

Yes, it can be done with .last  variables. I assumed the data has the structure you presented :

data visits;
length patient visit $10;
infile datalines missover;
input patient visit @;
do while (not missing(visit));
     output;
     input visit @;
     end;
datalines;
patientA clinic1 clinic2 clinic3 clinic1 clinic4 clinic1
PatientB clinic2 clinic3 clinic1 clinic2 clinic2
PatientC clinic1 clinic3 clinic1 clinic2 clinic2
;


data nbVisits(drop=visit);
do until(last.patient);
     set visits; by patient notsorted;
     nbVisits = sum(nbVisits, 1);
     end;
run;

proc sort data=nbVisits; by nbVisits; run;

data visitStats(drop=patient);
do until(last.nbVisits);
     set nbVisits; by nbVisits;
     nbPatients = sum(nbPatients, 1);
     end;
run;

proc print noobs; run;

PG

PG
SAS_new
Calcite | Level 5

Wow!  Thanks PG-  it worked.  So its a two data step part counting until reaching the last record for both steps.  Thank you

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 18 replies
  • 1621 views
  • 2 likes
  • 6 in conversation