Hello,
I have a dataset with an ID (id), a discharge date (dcdeathdate), screening (hcvscreening), screening result (hcvresult), and where they were screened (_organizationtax). The variable hcvscreening has three possible values: Yes, No, or a blank entry. The variable hcvresult has three possible values: Positive, Negative, or a blank entry.
From a previous post, we created a table that lists anyone with discrepant entries of hcvresult if they were screened more than once. The logic is this: for an associated id, if hcvresult=’Yes’ more than once, find those instances where there is a discrepancy of hcvresult. Meaning that there is at least 1 discrepancy of hcvresult (hcvresult from one entry is positive, whereas hcvresult from another entry is negative) from entries that only have hcvscreening as ‘Yes’. I had it listed out like this.
Dcdeathdate | ID | HCVScreening | HCVresult | _organizationtax |
01JAN17:00:00:00 | 1 | Yes | Negative | A |
15JAN17:00:00:00 | 1 | Yes | Positive | B |
20JAN17:00:00:00 | 1 | Yes | Negative | A |
29JAN17:00:00:00 | 1 | Yes | Positive | B |
I wanted to create a separate table which basically list all unique IDs within this new dataset and add a variable called "discreptype" with the following logic:
For the associated ID
If the earliest entry of HCVresult is Negative and the last entry is Positive, discreptype ='1'
If the earliest entry of HCVresult is Positive and the last entry is Negative, discreptype='2'
If anything else, discreptype='3'
ID | DiscrepType | _organizationtax |
1 | 1 | A |
2 | 2 | B |
3 | 1 | C |
Here is the original dataset:
data JANFEB.MULTIVISITS;
infile datalines dsd truncover;
input dcdeathdate:DATETIME16. id:$24. HCVScreening:$9. HCVresult:$27. _OrganizationTax:$9.;
datalines4;06FEB17:00:00:00,ც01347652,Yes,Negative,211385767
20JAN17:00:00:00,უ11602098,Yes,Negative,211385767
20FEB17:00:00:00,პ 1960105,Yes,Negative,211385767
16FEB17:00:00:00,ესპანეთი,Yes,Negative,211385767
30JAN17:00:00:00,აზერ4647829,Yes,Negative,406055879
06JAN17:00:00:00,ucxoeli,Yes,Negative,211385767
26FEB17:00:00:00,ucxoeli,Yes,Negative,211385767
24FEB17:00:00:00,ucnobi,Yes,Negative,211385767
14JAN17:00:00:00,arisucxoeli,Yes,Negative,211385767
28JAN17:00:00:00,arisucxoeli,Yes,Negative,211385767
28FEB17:00:00:00,arisucxoeli,Yes,Negative,211385767
09FEB17:00:00:00,"ar aris saq ,moqalqe",Yes,Negative,211385767
21JAN17:00:00:00,afxazeTismoqalaqe,Yes,Negative,211385767
27JAN17:00:00:00,P4689991,Yes,Negative,211385767
26FEB17:00:00:00,P4689991,Yes,Negative,211385767
13JAN17:00:00:00,P4171238,Yes,Negative,211385767
11FEB17:00:00:00,P4124336,Yes,Negative,211385767
24FEB17:00:00:00,P3724548,Yes,Negative,211385767
16FEB17:00:00:00,P3185235,Yes,Negative,211385767
17JAN17:00:00:00,C13607977,Yes,Negative,211385767
02FEB17:00:00:00,BA1266488,Yes,Negative,211385767
10FEB17:00:00:00,BA1266488,Yes,Negative,211385767
26JAN17:00:00:00,AP0665798,Yes,Negative,211385767
19JAN17:00:00:00,AN0558236,Yes,Negative,211385767
16JAN17:00:00:00,AN0518392,Yes,Negative,211385767
16JAN17:00:00:00,AN0254322,Yes,Negative,211385767
23FEB17:00:00:00,AN0244976,Yes,Negative,211385767
19JAN17:00:00:00,AM0840796,Yes,Negative,211385767
14JAN17:00:00:00,AM0734343,Yes,Negative,211385767
19JAN17:00:00:00,AM0667830,Yes,Negative,211385767
23JAN17:00:00:00,AM0649811,Yes,Negative,211385767
09FEB17:00:00:00,AM0467067,Yes,Negative,211385767
25JAN17:00:00:00,AK0688038,Yes,Negative,406055879
26JAN17:00:00:00,AH0691827,Yes,Negative,211385767
14JAN17:00:00:00,AH0483893,Yes,Negative,211385767
03FEB17:00:00:00,AF0735629,Yes,Negative,211385767
20JAN17:00:00:00,AF0411892,Yes,Negative,211385767
06FEB17:00:00:00,9015162946,Yes,Negative,201945271
27FEB17:00:00:00,75738000083,Yes,Negative,217879259
11FEB17:00:00:00,75711000023,Yes,Negative,404866123
04FEB17:00:00:00,75611000011,Yes,Negative,412682501
22JAN17:00:00:00,75341000001,Yes,Negative,400115362
28JAN17:00:00:00,75147000020,Yes,Negative,404907730
19JAN17:00:00:00,75137000004,Yes,Negative,245441552
13JAN17:00:00:00,75122000461,Yes,Negative,201954242
23JAN17:00:00:00,75122000461,No,,211328703
16JAN17:00:00:00,75111000020,No,,404896644
21JAN17:00:00:00,73 0505895,Yes,Negative,404514762
02FEB17:00:00:00,73 0505895,Yes,Negative,404514762
27FEB17:00:00:00,65955000228,Yes,Negative,231184232
18FEB17:00:00:00,65941001098,Yes,Negative,202463752
12JAN17:00:00:00,65908002543,Yes,Negative,202463752
23JAN17:00:00:00,65902014049,Yes,Negative,404879663
10JAN17:00:00:00,65901002274,No,,404865981
25JAN17:00:00:00,65809000131,Yes,Negative,216296639
20JAN17:00:00:00,65808002231,No,,404865963
18JAN17:00:00:00,65741000816,Yes,Negative,404865981
20JAN17:00:00:00,65714003280,Yes,Negative,206063383
25JAN17:00:00:00,65714003229,Yes,Negative,205210467
20JAN17:00:00:00,65714002992,No,,212153756
01FEB17:00:00:00,65708002618,No,,412682501
17FEB17:00:00:00,65609000206,No,,406055879
16FEB17:00:00:00,65541000842,Yes,Negative,205288295
01JAN17:00:00:00,65523000742,Yes,Negative,400115362
15FEB17:00:00:00,65509000224,Yes,Negative,404866123
14FEB17:00:00:00,65452000001,No,,412682501
24JAN17:00:00:00,65441001267,Yes,Negative,211328703
14FEB17:00:00:00,65441000999,Yes,Negative,412682501
06JAN17:00:00:00,65414003333,Yes,Negative,231169874
24FEB17:00:00:00,65408002382,No,,212841424
06FEB17:00:00:00,65314003181,Yes,Negative,211385767
04JAN17:00:00:00,65241001403,No,,406055879
08JAN17:00:00:00,65128000061,No,,204871594
11JAN17:00:00:00,65103001069,Yes,Negative,200010674
18JAN17:00:00:00,65103001069,Yes,Negative,202249968
21FEB17:00:00:00,65041000563,Yes,Negative,412682066
03JAN17:00:00:00,65041000414,Yes,Negative,404866123
07FEB17:00:00:00,65041000247,Yes,Negative,416289947
24FEB17:00:00:00,65023000292,Yes,Negative,416289947
07JAN17:00:00:00,65023000177,No,,404865963
19FEB17:00:00:00,65014002810,Yes,Positive,202948819
23FEB17:00:00:00,65014002333,No,,202948819
24FEB17:00:00:00,65014001733,Yes,Negative,401956433
12JAN17:00:00:00,65014001333,Yes,Negative,200010674
31JAN17:00:00:00,65014001109,Yes,Positive,404476205
10JAN17:00:00:00,65008002099,No,,412682501
17FEB17:00:00:00,65008002070,Yes,Negative,204871781
28JAN17:00:00:00,65008002009,Yes,Negative,205210467
10JAN17:00:00:00,65008001909,No,,412682501
28FEB17:00:00:00,65008001778,Yes,Negative,211385767
21FEB17:00:00:00,65008001385,Yes,Negative,202463752
03FEB17:00:00:00,65008001072,Yes,Negative,406131939
25FEB17:00:00:00,65008000855,Yes,Negative,203827608
24JAN17:00:00:00,65008000380,Yes,Negative,206047712
03FEB17:00:00:00,65008000368,No,,400115362
03FEB17:00:00:00,65008000212,No,Negative,404907730
01FEB17:00:00:00,65008000202,Yes,Negative,405032806
19JAN17:00:00:00,65008000008,Yes,Negative,201945271
25FEB17:00:00:00,65004000338,Yes,Negative,404854485
14FEB17:00:00:00,65002013151,No,,402022253
18FEB17:00:00:00,65002013151,Yes,Negative,402022253
10JAN17:00:00:00,65002012912,Yes,Negative,202050840
21JAN17:00:00:00,65002012697,No,,204871594
03FEB17:00:00:00,65002012697,No,,404896644
19JAN17:00:00:00,65002012584,Yes,Negative,203827608
29JAN17:00:00:00,65002012136,Yes,Negative,412682066
09JAN17:00:00:00,65002012059,Yes,Negative,203827608
05JAN17:00:00:00,65002011739,Yes,Negative,404476205
12FEB17:00:00:00,65002011532,Yes,Negative,212685414
25JAN17:00:00:00,65002011151,Yes,Negative,405069474
03JAN17:00:00:00,65002010872,Yes,Negative,212691354
08JAN17:00:00:00,65002010783,No,,202051689
06JAN17:00:00:00,65002010544,Yes,Negative,400123647
17FEB17:00:00:00,65002010459,Yes,Negative,212806766
25JAN17:00:00:00,65002010232,No,,212153756
10FEB17:00:00:00,65002010232,Yes,Positive,405018831
24JAN17:00:00:00,65002009866,Yes,Negative,212672080
20FEB17:00:00:00,65002009449,Yes,Negative,211385767
23FEB17:00:00:00,65002009335,Yes,Positive,404869567
04JAN17:00:00:00,65002008923,Yes,Negative,404879663
20JAN17:00:00:00,65002008744,Yes,Positive,202193544
12JAN17:00:00:00,65002008542,No,,202051689
16JAN17:00:00:00,65002008495,Yes,Negative,212841424
06JAN17:00:00:00,65002008122,Yes,Negative,231169507
10FEB17:00:00:00,65002008014,Yes,Negative,211385767
04FEB17:00:00:00,65002007581,Yes,Negative,402006592
21FEB17:00:00:00,65002007520,Yes,Negative,406131939
10FEB17:00:00:00,65002007511,Yes,Negative,206120730
21FEB17:00:00:00,65002006633,Yes,Negative,202051689
05FEB17:00:00:00,65002006208,Yes,Positive,404476205
20FEB17:00:00:00,65002005336,Yes,Negative,211385767
09FEB17:00:00:00,65002005109,Yes,Negative,203827608
11JAN17:00:00:00,65002005018,Yes,Negative,405049335
06FEB17:00:00:00,65002004855,Yes,Negative,204970022
03FEB17:00:00:00,65002004782,Yes,Negative,212672080
20FEB17:00:00:00,65002004015,Yes,Negative,202051876
25JAN17:00:00:00,65002003037,Yes,Negative,404941827
15JAN17:00:00:00,65002002630,Yes,Positive,404907730
19JAN17:00:00:00,65002002423,Yes,Negative,404514762
25FEB17:00:00:00,65002002111,Yes,Negative,205250618
10JAN17:00:00:00,65002001819,Yes,Negative,202442981
16JAN17:00:00:00,65002001616,No,,202051689
31JAN17:00:00:00,65002001247,Yes,Negative,206047464
21FEB17:00:00:00,65002001193,Yes,Negative,202051876
16JAN17:00:00:00,65002001180,Yes,Negative,212685423
18JAN17:00:00:00,65002000404,Yes,Negative,212002580
20JAN17:00:00:00,65002000360,Yes,Negative,404866123
26JAN17:00:00:00,65001001052,Yes,Negative,202463752
27JAN17:00:00:00,65001000908,No,,404896644
17JAN17:00:00:00,62909014878,Yes,Negative,212841424
10JAN17:00:00:00,62909014828,Yes,Negative,205218030
24JAN17:00:00:00,62909011945,Yes,Negative,203827608
23JAN17:00:00:00,62909010597,No,,245428434
28JAN17:00:00:00,62909009733,Yes,Negative,404476205
20FEB17:00:00:00,62909009263,Yes,Negative,405001466
06FEB17:00:00:00,62909008385,Yes,Negative,404908043
07FEB17:00:00:00,62909008385,Yes,Negative,202193544
30JAN17:00:00:00,62902023816,Yes,Positive,212153756
03FEB17:00:00:00,62902023816,Yes,Positive,202193544
14FEB17:00:00:00,62902023816,Yes,Positive,212153756
20JAN17:00:00:00,62902022968,No,,412682501
30JAN17:00:00:00,62902021941,No,,404896644
01FEB17:00:00:00,62902020223,No,,212002580
28FEB17:00:00:00,62902019998,No,,404896644
17JAN17:00:00:00,62902019218,No,,204871594
24JAN17:00:00:00,62902015217,No,,404896644
30JAN17:00:00:00,62902014720,Yes,Negative,404865981
21JAN17:00:00:00,62902014489,Yes,Negative,402022253
30JAN17:00:00:00,62902013462,Yes,Negative,211328703
01FEB17:00:00:00,62902013322,Yes,Negative,211385767
10JAN17:00:00:00,62902013152,Yes,Negative,400115362
29JAN17:00:00:00,62902012694,No,Negative,417876711
23JAN17:00:00:00,62902012594,Yes,Negative,404896644
15JAN17:00:00:00,62902011266,No,,239866542
09JAN17:00:00:00,62902009614,Yes,Negative,404865981
08FEB17:00:00:00,62902009344,Yes,Negative,404476205
21JAN17:00:00:00,62902008766,Yes,Negative,412682066
19JAN17:00:00:00,6287090,Yes,Negative,201945271
17JAN17:00:00:00,62809013908,Yes,Negative,404866123
06JAN17:00:00:00,62809013388,Yes,Negative,405064594
02JAN17:00:00:00,62809012991,No,,204871594
13FEB17:00:00:00,62809011603,Yes,Negative,404476205
01FEB17:00:00:00,62809009791,No,,212708239
01JAN17:00:00:00,62802023174,Yes,Positive,412682501
01FEB17:00:00:00,62802020171,Yes,Negative,405001466
02FEB17:00:00:00,62802018858,Yes,Negative,211328703
04JAN17:00:00:00,62802018108,Yes,Negative,404865981
24FEB17:00:00:00,62802010046,No,,404896644
18JAN17:00:00:00,62802008134,Yes,Negative,404865981
16FEB17:00:00:00,62780000293,No,Negative,205017505
08FEB17:00:00:00,62709012018,Yes,Negative,404467019
15FEB17:00:00:00,62709010953,Yes,Positive,215119182
18JAN17:00:00:00,62709010323,Yes,Negative,202463752
13JAN17:00:00:00,62702023572,Yes,Negative,400115362
20JAN17:00:00:00,62702023112,No,,209446900
14JAN17:00:00:00,62702022314,No,,412682501
10FEB17:00:00:00,62702018456,Yes,Negative,200010022
14FEB17:00:00:00,62702017318,Yes,Negative,202463752
26FEB17:00:00:00,62702016941,Yes,Negative,215119182
13FEB17:00:00:00,62702016681,Yes,Negative,404869567;;;;
This is the code we used to get the first sql table:
proc sql;
create table janfeb.multiresult as
select *
from JANFEB.jfg
where nationalid NE '' and HCVresult NE ''
group by nationalid
having count(distinct HCVresult) > 1
order by nationalid, dcdeathdate;
quit;
PROC SQL;
CREATE TABLE janfeb.multivisitscount AS
SELECT NationalId
,COUNT(*) AS Visits
FROM janfeb.multiresult
GROUP BY NationalId
HAVING COUNT(*) > 1
ORDER BY NationalId;
QUIT;
Proc sort data=janfeb.multivisitscount;
by descending visits;
run;
Thanks for your help!
Click image to register for webinar
