DATA Step, Macro, Functions and more

Multiple visit count

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

Multiple visit count

Hello,

 

I have a dataset with an ID (id) and a discharge date (dcdeathdate). I would like to create a table that tells me for every person that is in the dataset for more than one entry, how many times do they appear in the dataset? Here is generally what I would imagine my output to be:

 

multiplevisitct.png

Here is a sample dataset

data JANFEB.SAMPLESAS;
  infile datalines dsd truncover;
  input dcdeathdate:DATETIME16. nationalid:$24. HCVScreening:$9. HCVresult:$27.;
datalines4;
02JAN17:00:00:00,61009030817,Yes,Negative
03JAN17:00:00:00,40001035344,Yes,Negative
03JAN17:00:00:00,12301107155,Yes,Negative
03JAN17:00:00:00,61006025798,Yes,Negative
03JAN17:00:00:00,33001047104,Yes,Negative
03JAN17:00:00:00,61007000494,Yes,Negative
03JAN17:00:00:00,61001066538,Yes,Negative
04JAN17:00:00:00,61004056218,Yes,Negative
05JAN17:00:00:00,61005011122,Yes,Negative
05JAN17:00:00:00,61001073560,Yes,Negative
04JAN17:00:00:00,61004058102,Yes,Negative
04JAN17:00:00:00,61001083691,Yes,Negative
06JAN17:00:00:00,61609034324,Yes,Negative
01JAN17:00:00:00,61006057544,Yes,Negative
04JAN17:00:00:00,61006027847,Yes,Negative
04JAN17:00:00:00,47001046248,Yes,Negative
04JAN17:00:00:00,33001043903,Yes,Negative
01JAN17:00:00:00,21001033914,Yes,Negative
01JAN17:00:00:00,56001023262,Yes,Negative
07JAN17:00:00:00,61001052514,Yes,Negative
02JAN17:00:00:00,54001057453,Yes,Negative
01JAN17:00:00:00,54001033500,Yes,Negative
01JAN17:00:00:00,54001048306,Yes,Negative
01JAN17:00:00:00,54001002655,Yes,Negative
02JAN17:00:00:00,54001050638,Yes,Negative
03JAN17:00:00:00,54001035443,Yes,Negative
02JAN17:00:00:00,54001026613,Yes,Negative
04JAN17:00:00:00,54001043440,Yes,Negative
02JAN17:00:00:00,54001030300,Yes,Negative
04JAN17:00:00:00,54001024199,Yes,Negative
04JAN17:00:00:00,54001033961,Yes,Negative
03JAN17:00:00:00,54750003030,Yes,Negative
04JAN17:00:00:00,54650002459,Yes,Negative
05JAN17:00:00:00,54450002965,Yes,Negative
05JAN17:00:00:00,54001046156,Yes,Negative
08JAN17:00:00:00,31001008869,Yes,Negative
02JAN17:00:00:00,53001059264,Yes,Negative
03JAN17:00:00:00,17001030838,Yes,Negative
03JAN17:00:00:00,60450050088,No,
03JAN17:00:00:00,21001035458,Yes,Negative
03JAN17:00:00:00,60001133497,Yes,Negative
04JAN17:00:00:00,62013000750,Yes,Negative
03JAN17:00:00:00,60350023266,No,
04JAN17:00:00:00,60350049582,No,
05JAN17:00:00:00,56001021291,Yes,Negative
05JAN17:00:00:00,60350050046,No,
02JAN17:00:00:00,53001006283,Yes,Negative
01JAN17:00:00:00,26001021610,01,02
02JAN17:00:00:00,40001002963,Yes,Negative
02JAN17:00:00:00,40001036105,Yes,Negative
01JAN17:00:00:00,01016007752,Yes,Negative
02JAN17:00:00:00,01003014737,Yes,Negative
02JAN17:00:00:00,25001044101,Yes,Negative
01JAN17:00:00:00,01017032355,Yes,Negative
03JAN17:00:00:00,62007012811,Yes,Negative
02JAN17:00:00:00,61601091933,No,Negative
03JAN17:00:00:00,61008017940,No,Negative
04JAN17:00:00:00,61001077448,No,Negative
03JAN17:00:00:00,32901030912,No,Negative
04JAN17:00:00:00,61008017410,No,Negative
02JAN17:00:00:00,37001052489,Yes,Negative
01JAN17:00:00:00,37001050822,Yes,Negative
01JAN17:00:00:00,60001067242,Yes,Negative
01JAN17:00:00:00,62001008421,Yes,Positive
01JAN17:00:00:00,17001016452,Yes,Negative
01JAN17:00:00:00,02001016868,Yes,Negative
01JAN17:00:00:00,26001012712,Yes,Negative
01JAN17:00:00:00,60001096862,Yes,Negative
01JAN17:00:00:00,62004017174,Yes,Negative
01JAN17:00:00:00,01013002451,Yes,Negative
03JAN17:00:00:00,53001049071,Yes,Negative
03JAN17:00:00:00,26001005685,Yes,Negative
03JAN17:00:00:00,29001032044,Yes,Negative
03JAN17:00:00:00,02001007836,Yes,Negative
03JAN17:00:00:00,39001027505,Yes,Negative
03JAN17:00:00:00,55001021898,Yes,Negative
02JAN17:00:00:00,37001024997,Yes,Negative
02JAN17:00:00:00,48001015445,Yes,Negative
02JAN17:00:00:00,19001049337,Yes,Negative
03JAN17:00:00:00,51001022180,Yes,Positive
03JAN17:00:00:00,18001066923,Yes,Negative
03JAN17:00:00:00,21001006112,Yes,Negative
04JAN17:00:00:00,33001066425,Yes,Negative
04JAN17:00:00:00,60001142037,Yes,Negative
05JAN17:00:00:00,61006025817,Yes,Negative
04JAN17:00:00:00,17001033070,Yes,Negative
04JAN17:00:00:00,17001030112,Yes,Negative
05JAN17:00:00:00,17001003279,Yes,Negative
05JAN17:00:00:00,53001024096,Yes,Negative
04JAN17:00:00:00,60001023215,Yes,Negative
05JAN17:00:00:00,33001038020,Yes,Negative
05JAN17:00:00:00,54650000288,Yes,Negative
05JAN17:00:00:00,34350000056,Yes,Positive
05JAN17:00:00:00,48001015163,Yes,Negative
02JAN17:00:00:00,29001031096,Yes,Negative
02JAN17:00:00:00,54001026383,Yes,Negative
04JAN17:00:00:00,60550011261,Yes,Negative
05JAN17:00:00:00,60001119863,Yes,Negative
05JAN17:00:00:00,49001013705,Yes,Negative
06JAN17:00:00:00,58001025379,Yes,Negative
06JAN17:00:00:00,41801032212,Yes,Negative
06JAN17:00:00:00,39001039352,Yes,Negative
06JAN17:00:00:00,60350000156,Yes,Negative
06JAN17:00:00:00,53001052829,Yes,Negative
06JAN17:00:00:00,60001033994,Yes,Negative
06JAN17:00:00:00,29001008173,Yes,Negative
06JAN17:00:00:00,29001016396,Yes,Negative
06JAN17:00:00:00,60001089496,Yes,Negative
06JAN17:00:00:00,29001017126,Yes,Negative
06JAN17:00:00:00,60001080494,Yes,Negative
06JAN17:00:00:00,60001095567,Yes,Negative
06JAN17:00:00:00,53001005379,Yes,Positive
06JAN17:00:00:00,60001062807,Yes,Negative
04JAN17:00:00:00,60801161960,Yes,Negative
06JAN17:00:00:00,37001054114,Yes,Negative
06JAN17:00:00:00,54001037789,Yes,Negative
06JAN17:00:00:00,60001026455,Yes,Negative
06JAN17:00:00:00,53001002545,Yes,Negative
08JAN17:00:00:00,60001031053,Yes,Negative
08JAN17:00:00:00,21001027850,No,
09JAN17:00:00:00,60001088492,Yes,Negative
09JAN17:00:00:00,17001025174,Yes,Negative
09JAN17:00:00:00,21901044267,Yes,Negative
07JAN17:00:00:00,60001101658,Yes,Negative
09JAN17:00:00:00,60350013412,Yes,Negative
07JAN17:00:00:00,29001004492,Yes,Negative
07JAN17:00:00:00,26001029583,Yes,Negative
07JAN17:00:00:00,60001034398,Yes,Negative
07JAN17:00:00:00,21001027582,Yes,Negative
07JAN17:00:00:00,54001052070,Yes,Negative
08JAN17:00:00:00,19001050036,Yes,Negative
08JAN17:00:00:00,55001014547,Yes,Negative
09JAN17:00:00:00,56001006814,Yes,Negative
09JAN17:00:00:00,60001072488,Yes,Negative
09JAN17:00:00:00,19001058154,Yes,Negative
09JAN17:00:00:00,19001066458,Yes,Negative
06JAN17:00:00:00,60750028364,Yes,Negative
07JAN17:00:00:00,62009003541,Yes,Negative
06JAN17:00:00:00,60001039686,Yes,Negative
10JAN17:00:00:00,53001020668,Yes,Negative
06JAN17:00:00:00,55001012370,Yes,Negative
10JAN17:00:00:00,55001016143,Yes,Negative
10JAN17:00:00:00,60001067494,Yes,Negative
05JAN17:00:00:00,53001022947,Yes,Positive
10JAN17:00:00:00,18250006495,Yes,Negative
07JAN17:00:00:00,60401169143,Yes,Negative
09JAN17:00:00:00,62004024350,Yes,Negative
09JAN17:00:00:00,26001032306,Yes,Negative
09JAN17:00:00:00,60001045447,Yes,Negative
09JAN17:00:00:00,21001039514,Yes,Negative
09JAN17:00:00:00,37001058293,Yes,Negative
09JAN17:00:00:00,60003012263,Yes,Negative
07JAN17:00:00:00,60950005599,Yes,Negative
06JAN17:00:00:00,60901163357,Yes,Negative
07JAN17:00:00:00,18001013721,Yes,Negative
07JAN17:00:00:00,60002005030,Yes,Negative
07JAN17:00:00:00,42001013310,Yes,Positive
03JAN17:00:00:00,55001020098,Yes,Negative
07JAN17:00:00:00,33001080088,Yes,Negative
04JAN17:00:00:00,60401171465,Yes,Negative
07JAN17:00:00:00,62005025475,Yes,Negative
07JAN17:00:00:00,29001015089,Yes,Negative
10JAN17:00:00:00,62002007047,Yes,Negative
10JAN17:00:00:00,38001047365,Yes,Negative
10JAN17:00:00:00,53001052381,Yes,Negative
10JAN17:00:00:00,62001002141,Yes,Negative
10JAN17:00:00:00,19001074511,Yes,Negative
10JAN17:00:00:00,37001015975,Yes,Negative
10JAN17:00:00:00,37001038228,Yes,Negative
08JAN17:00:00:00,39450008328,Yes,Negative
05JAN17:00:00:00,49001014679,Yes,Negative
03JAN17:00:00:00,53001042377,Yes,Positive
03JAN17:00:00:00,37001022213,Yes,Negative
04JAN17:00:00:00,49001005836,No,
04JAN17:00:00:00,48001021577,Yes,Negative
04JAN17:00:00:00,60001110257,No,
05JAN17:00:00:00,55001013700,No,
06JAN17:00:00:00,41001008969,Yes,Negative
06JAN17:00:00:00,19001072588,Yes,Positive
06JAN17:00:00:00,17001025437,No,
07JAN17:00:00:00,51001019825,Yes,Negative
07JAN17:00:00:00,58001024813,No,
07JAN17:00:00:00,60001132085,Yes,Negative
07JAN17:00:00:00,53001037330,Yes,Negative
08JAN17:00:00:00,41001003669,Yes,Negative
08JAN17:00:00:00,21001028315,Yes,Negative
08JAN17:00:00:00,60001159843,Yes,Negative
09JAN17:00:00:00,60001058815,Yes,Negative
10JAN17:00:00:00,58001012621,Yes,Negative
10JAN17:00:00:00,18001023501,Yes,Negative
10JAN17:00:00:00,42001029315,Yes,Negative
05JAN17:00:00:00,01011094651,Yes,Negative
05JAN17:00:00:00,24001045773,Yes,Negative
04JAN17:00:00:00,37001020655,Yes,Negative
01JAN17:00:00:00,01030041913,Yes,Negative
02JAN17:00:00:00,24001040015,No,
02JAN17:00:00:00,16001013481,No,
02JAN17:00:00:00,01030034930,No,
03JAN17:00:00:00,01019045369,Yes,Negative
03JAN17:00:00:00,60001022570,Yes,Negative
;;;;

Thank you!


Accepted Solutions
Solution
‎03-31-2017 03:03 AM
New Contributor
Posts: 3

Re: Multiple visit count

I think this is what you are looking for:

 

PROC SQL;

  CREATE TABLE Visits AS

  SELECT NationalId

        ,COUNT(*) AS Visits

  FROM SampleSAS

  GROUP BY NationalId

  HAVING COUNT(*) > 1

  ORDER BY NationalId;

QUIT;

View solution in original post


All Replies
Valued Guide
Posts: 505

Re: Multiple visit count

There are no ids with multiple visits in your sample data?

data wrap;
  set samplesas;
  put nationalid @@;
run;quit;

data have;

input id:$12. @@;
cards4;
61009030817 40001035344 12301107155 61006025798 33001047104 61007000494 61001066538
61004056218 61005011122 61001073560 61004058102 61001083691 61609034324 61006057544
61006027847 47001046248 33001043903 21001033914 56001023262 61001052514 54001057453
54001033500 54001048306 54001002655 54001050638 54001035443 54001026613 54001043440
54001030300 54001024199 54001033961 54750003030 54650002459 54450002965 54001046156
31001008869 53001059264 17001030838 60450050088 21001035458 60001133497 62013000750
60350023266 60350049582 56001021291 60350050046 53001006283 26001021610 40001002963
40001036105 01016007752 01003014737 25001044101 01017032355 62007012811 61601091933
61008017940 61001077448 32901030912 61008017410 37001052489 37001050822 60001067242
62001008421 17001016452 02001016868 26001012712 60001096862 62004017174 01013002451
53001049071 26001005685 29001032044 02001007836 39001027505 55001021898 37001024997
48001015445 19001049337 51001022180 18001066923 21001006112 33001066425 60001142037
61006025817 17001033070 17001030112 17001003279 53001024096 60001023215 33001038020
54650000288 34350000056 48001015163 29001031096 54001026383 60550011261 60001119863
49001013705 58001025379 41801032212 39001039352 60350000156 53001052829 60001033994
29001008173 29001016396 60001089496 29001017126 60001080494 60001095567 53001005379
60001062807 60801161960 37001054114 54001037789 60001026455 53001002545 60001031053
21001027850 60001088492 17001025174 21901044267 60001101658 60350013412 29001004492
26001029583 60001034398 21001027582 54001052070 19001050036 55001014547 56001006814
60001072488 19001058154 19001066458 60750028364 62009003541 60001039686 53001020668
55001012370 55001016143 60001067494 53001022947 18250006495 60401169143 62004024350
26001032306 60001045447 21001039514 37001058293 60003012263 60950005599 60901163357
18001013721 60002005030 42001013310 55001020098 33001080088 60401171465 62005025475
29001015089 62002007047 38001047365 53001052381 62001002141 19001074511 37001015975
37001038228 39450008328 49001014679 53001042377 37001022213 49001005836 48001021577
60001110257 55001013700 41001008969 19001072588 17001025437 51001019825 58001024813
60001132085 53001037330 41001003669 21001028315 60001159843 60001058815 58001012621
18001023501 42001029315 01011094651 24001045773 37001020655 01030041913 24001040015
16001013481 01030034930 01019045369 60001022570
;;;;
run;quit;

proc sql;
  create
    table want as
  select
    count(*) as repeats
  from
    have
  group
    by id
  having
    count(id)>1
;quit;

NOTE: Table WORK.WANT created, with 0 rows and 1 columns.


proc freq data=have nlevels order=freq;
tables id / out=want missing;
run;quit;

Number of Variable Levels

Variable      Levels
--------------------
ID               200


MOST FREQUENT TO LEAST FREQUENT

Up to 40 obs from have total obs=200

The FREQ Procedure

                                        Cumulative    Cumulative
ID             Frequency     Percent     Frequency      Percent
----------------------------------------------------------------
01003014737           1        0.50             1         0.50
01011094651           1        0.50             2         1.00
01013002451           1        0.50             3         1.50
01016007752           1        0.50             4         2.00
01017032355           1        0.50             5         2.50
01019045369           1        0.50             6         3.00
01030034930           1        0.50             7         3.50
01030041913           1        0.50             8         4.00
02001007836           1        0.50             9         4.50
02001016868           1        0.50            10         5.00




Solution
‎03-31-2017 03:03 AM
New Contributor
Posts: 3

Re: Multiple visit count

I think this is what you are looking for:

 

PROC SQL;

  CREATE TABLE Visits AS

  SELECT NationalId

        ,COUNT(*) AS Visits

  FROM SampleSAS

  GROUP BY NationalId

  HAVING COUNT(*) > 1

  ORDER BY NationalId;

QUIT;

Super User
Posts: 10,538

Re: Multiple visit count

From the variable name DCDeathDate it looks like you are looking for people that died more than once??Smiley Surprised? Is the zombie apocalypse on us?

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 161 views
  • 2 likes
  • 4 in conversation