Proc Sql help with creating new variable based on old table

byeh2017 — Tue, 11 Apr 2017 12:46:23 GMT

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!

Re: Proc Sql help with creating new variable based on old table

art297 — Tue, 11 Apr 2017 15:33:50 GMT

Where does the file used in your initial proc sql call come from? i.e.:

	from JANFEB.jfg

Also, there are a bunch of irregular characters in the dataset you provided. Are they supposed to be there?

Art, CEO, AnalystFinder.com

topic Proc Sql help with creating new variable based on old table in SAS Programming

Proc Sql help with creating new variable based on old table

Re: Proc Sql help with creating new variable based on old table