Solved: Drawing data from second visit to carry over for all patient data

jpsmith · Posted 05-29-2018 07:27 PM

Hello,

I am working with a longitudinal dataset from which I need baseline data to carry over across all visits for the patient. Typically I use the retain statement first.obs approach to carry over baseline data. However, in this dataset, "baseline" data are contained in both the first and second visits (which are both the same visit). Demographic variables, such as age and sex, are included in the "first visit" (visitno=1) and laboratory data such as CD4 count are stored within the second visit (visitno=2). Both of these variable types need to be carried over across all visits as new variables for baseline values (i.e. baseline CD4).

For the demographic variables, the retain/first.obs works fine. It is the laboratory variables that I cannot figure out.

An example of the data formats I have and want are below, as well as the data2datastep. Note, the visit numbers are a bit misleading: visits 1-2 are baseline, so visit 3 is the first follow up, visit 4 is the second, and so on. Also, the "culture" variable can be ignored.

Running SAS 9.4

Thank you!

Jonathan

*******************************

Examples of have/wants:

Data: Have
PID	VISITNO	GENDER	AGE	CULTURE	CD4RES	VLRES
1001	1	1	23
1001	2			1	500	10000
1001	3			1	550	12000
1002	1	1	43
1002	2			1	425	120000
1002	3			1	400	134200
1002	4			0	550	132110
1003	1	0	67	0
1003	2			1	900	90000
1003	3			1	800	150000
1003	4			0	850	13000

I want it to look like this:

Data: Want
PID	VISITNO	GENDER	AGE	CULTURE	CD4RES	VLRES	CD4_bl	vl_bl
1001	1	1	23				500	10000
1001	2	1	23	1	500	10000	500	10000
1001	3	1	23	0	550	12000	500	10000
1002	1	1	43				425	120000
1002	2	1	43	1	425	120000	425	120000
1002	3	1	43	1	400	134200	425	120000
1002	4	1	43	0	550	132110	425	120000
1003	1	0	67	0			900	90000
1003	2	0	67	1	900	90000	900	90000
1003	3	0	67	1	800	150000	900	90000
1003	4	0	67	0	850	13000	900	90000

where *_bl=baseline results

the code for data2datastep is below:

data WORK.HAVE;
  infile datalines dsd truncover;
  input GENDER:32. AGE:32. CULTURE:32. CD4RES:32. VLRES:32. pid:32. visitno:32.;
  label GENDER="GENDER" AGE="AGE" CULTURE="CULTURE" CD4RES="CD4RES" VLRES="VLRES";
datalines4;
2,48,,,,50200003,1
,,2,14,837504,50200003,2
,,,,,50200003,12
1,52,,,,50200005,1
,,7,112,119981,50200005,2
,,,276,229656000,50200005,3
,,,,,50200005,4
,,,,,50200005,5
,,,,,50200005,6
,,,,,50200005,7
,,,,,50200005,8
,,,,,50200005,9
,,,,,50200005,10
2,31,,,,50200006,1
,,7,66,204960000,50200006,2
,,,72,379880000,50200006,3
2,37,,,,50200007,1
,,7,17,251390000,50200007,2
,,,,,50200007,12
2,42,,,,50200008,1
,,7,51,679,50200008,2
,,,112,50,50200008,3
,,,,,50200008,4
,,,,,50200008,5
,,,,,50200008,6
,,,,,50200008,7
,,,,,50200008,8
,,,,,50200008,9
,,,,,50200008,10
1,36,,,,50200010,1
,,7,84,104957,50200010,2
,,,71,277,50200010,3
,,,,,50200010,4
,,,,,50200010,7
,,,,,50200010,8
,,,,,50200010,9
,,,,,50200010,10
1,47,,,,50200011,1
,,,147,232304,50200011,2
,,,250,789,50200011,3
,,,,,50200011,4
,,,,,50200011,5
,,,,,50200011,6
,,,,,50200011,7
,,,,,50200011,8
,,,,,50200011,9
,,,,,50200011,10
1,37,,,,50200012,1
,,7,222,176728000,50200012,2
,,,243,239,50200012,3
,,,,,50200012,4
,,,,,50200012,5
,,,,,50200012,6
,,,,,50200012,7
,,,,,50200012,8
,,,,,50200012,9
,,,,,50200012,10
2,35,,,,50200013,1
,,7,48,129591,50200013,2
,,,196,887,50200013,3
,,,,,50200013,4
,,,,,50200013,5
,,,,,50200013,6
,,,,,50200013,7
,,,,,50200013,8
,,,,,50200013,9
,,,,,50200013,10
2,31,,,,50200014,1
,,7,197,457600000,50200014,2
,,,141,6113,50200014,3
,,,,,50200014,4
,,,,,50200014,5
,,,,,50200014,6
,,,,,50200014,7
,,,,,50200014,8
,,,,,50200014,9
,,,,,50200014,10
1,36,,,,50200015,1
,,7,57,244357000,50200015,2
,,,,,50200015,12
2,24,,,,50200016,1
,,7,129,311039000,50200016,2
,,,208,315,50200016,3
,,,,,50200016,4
,,,,,50200016,5
,,,,,50200016,6
,,,,,50200016,7
,,,,,50200016,8
,,,,,50200016,9
,,,,,50200016,10
2,36,,,,50200017,1
,,7,135,886960000,50200017,2
,,,206,203,50200017,3
,,,,,50200017,5
,,,,,50200017,6
,,,,,50200017,7
,,,,,50200017,8
,,,,,50200017,9
,,,,,50200017,10
1,58,,,,50200018,1
,,7,94,451330000,50200018,2
,,,,,50200018,12
2,27,,,,50200020,1
,,7,59,532530000,50200020,2
,,,85,477,50200020,3
1,35,,,,50200021,1
,,7,162,17311,50200021,2
,,,,,50200021,4
,,,,,50200021,5
,,,,,50200021,6
,,,,,50200021,7
,,,,,50200021,8
,,,,,50200021,9
,,,,,50200021,10
2,30,,,,50200024,1
,,7,156,95684,50200024,2
,,,,,50200024,4
,,,,,50200024,5
,,,,,50200024,6
,,,,,50200024,7
,,,,,50200024,8
,,,,,50200024,9
,,,,,50200024,10
2,34,,,,50200025,1
,,7,47,788871,50200025,2
2,29,,,,50200026,1
,,7,137,275102,50200026,2
,,,28,637,50200026,3
,,,,,50200026,4
,,,,,50200026,5
,,,,,50200026,6
,,,,,50200026,7
,,,,,50200026,8
,,,,,50200026,9
,,,,,50200026,10
1,32,,,,50200027,1
,,7,188,161462,50200027,2
,,,299,324,50200027,3
,,,,,50200027,4
,,,,,50200027,5
,,,,,50200027,6
,,,,,50200027,7
,,,,,50200027,8
,,,,,50200027,9
,,,,,50200027,10
2,24,,,,50200028,1
,,7,36,390496,50200028,2
,,,264,375,50200028,3
,,,,,50200028,5
,,,,,50200028,6
,,,,,50200028,7
,,,,,50200028,8
,,,,,50200028,9
1,36,,,,50200029,1
,,7,48,571896,50200029,2
,,,,,50200029,12
2,40,,,,50200030,1
,,7,92,1535405,50200030,2
,,,,,50200030,5
,,,,,50200030,6
,,,,,50200030,7
,,,,,50200030,8
,,,,,50200030,9
,,,,,50200030,10
1,44,,,,50200031,1
,,7,46,3004999,50200031,2
,,,45,500,50200031,3
1,35,,,,50200033,1
,,7,29,167301,50200033,2
,,,61,578,50200033,3
2,34,,,,50200034,1
,,7,17,97496,50200034,2
,,,42,69202,50200034,3
,,,,,50200034,4
,,,,,50200034,5
,,,,,50200034,6
,,,,,50200034,7
,,,,,50200034,8
,,,,,50200034,9
,,,,,50200034,10
2,24,,,,50200035,1
,,7,70,225474,50200035,2
,,,139,487,50200035,3
,,,,,50200035,4
,,,,,50200035,5
,,,,,50200035,6
,,,,,50200035,7
,,,,,50200035,8
,,,,,50200035,9
,,,,,50200035,10
1,36,,,,50200036,1
,,7,270,911759,50200036,2
2,37,,,,50200037,1
,,7,23,153966,50200037,2
,,,120,309,50200037,3
,,,,,50200037,4
,,,,,50200037,6
,,,,,50200037,7
,,,,,50200037,8
,,,,,50200037,9
1,29,,,,50200038,1
,,7,75,139801,50200038,2
,,,171,280,50200038,3
1,52,,,,50200039,1
,,7,114,44825,50200039,2
,,,180,651,50200039,3
,,,,,50200039,4
,,,,,50200039,5
,,,,,50200039,6
,,,,,50200039,7
,,,,,50200039,8
,,,,,50200039,9
,,,,,50200039,10
1,37,,,,50200041,1
,,7,107,69383,50200041,2
,,,177,101156,50200041,3
,,,,,50200041,4
,,,,,50200041,5
,,,,,50200041,6
,,,,,50200041,7
,,,,,50200041,8
,,,,,50200041,9
,,,,,50200041,10
1,50,,,,50200042,1
,,7,,,50200042,2
2,38,,,,50200044,1
,,7,63,299125,50200044,2
,,,133,489,50200044,3
,,,,,50200044,4
,,,,,50200044,5
,,,,,50200044,6
,,,,,50200044,7
,,,,,50200044,8
2,25,,,,50200045,1
,,7,248,25243,50200045,2
,,,297,161,50200045,3
,,,,,50200045,5
,,,,,50200045,6
,,,,,50200045,7
,,,,,50200045,8
,,,,,50200045,9
,,,,,50200045,10
2,33,,,,50200048,1
,,7,209,93700,50200048,2
,,,,,50200048,4
,,,,,50200048,5
,,,,,50200048,6
,,,,,50200048,7
,,,,,50200048,8
,,,,,50200048,9
,,,,,50200048,10
1,47,,,,50200049,1
,,7,191,427300,50200049,2
,,,,,50200049,12
1,37,,,,50200051,1
,,7,180,1608104,50200051,2
,,,,,50200051,4
,,,,,50200051,5
,,,,,50200051,6
,,,,,50200051,7
,,,,,50200051,8
,,,,,50200051,9
,,,,,50200051,10
1,51,,,,50200052,1
,,7,84,637789,50200052,2
,,,148,336,50200052,3
,,,,,50200052,4
,,,,,50200052,5
,,,,,50200052,6
,,,,,50200052,7
,,,,,50200052,8
,,,,,50200052,9
,,,,,50200052,10
1,32,,,,50200053,1
,,7,200,160972,50200053,2
,,,180,4837,50200053,3
,,,,,50200053,4
,,,,,50200053,5
1,29,,,,50200054,1
,,7,220,237608,50200054,2
,,,206,159,50200054,3
,,,,,50200054,4
,,,,,50200054,5
,,,,,50200054,6
,,,,,50200054,7
,,,,,50200054,8
,,,,,50200054,9
,,,,,50200054,10
1,47,,,,50200059,1
,,7,164,973870,50200059,2
,,,643,1038,50200059,3
,,,,,50200059,4
,,,,,50200059,5
,,,,,50200059,6
,,,,,50200059,7
,,,,,50200059,8
,,,,,50200059,9
,,,,,50200059,10
2,40,,,,50200062,1
,,7,7,145945,50200062,2
,,,74,344,50200062,3
,,,,,50200062,4
,,,,,50200062,5
,,,,,50200062,6
,,,,,50200062,7
,,,,,50200062,8
,,,,,50200062,9
,,,,,50200062,10
1,49,,,,50200066,1
,,7,62,2455262,50200066,2
,,,141,176,50200066,3
,,,,,50200066,4
,,,,,50200066,5
,,,,,50200066,6
,,,,,50200066,7
,,,,,50200066,8
,,,,,50200066,9
,,,,,50200066,10
2,30,,,,50200067,1
,,7,50,225804,50200067,2
,,,,,50200067,12
1,48,,,,50200068,1
,,7,40,717818,50200068,2
,,,103,491,50200068,3
,,,,,50200068,4
,,,,,50200068,5
,,,,,50200068,6
,,,,,50200068,7
,,,,,50200068,8
,,,,,50200068,9
,,,,,50200068,10
1,38,,,,50200069,1
,,7,41,1016262,50200069,2
,,,,,50200069,3
,,,,,50200069,4
,,,,,50200069,7
1,47,,,,50200070,1
,,7,49,88521,50200070,2
,,,183,205,50200070,3
,,,,,50200070,4
,,,,,50200070,5
,,,,,50200070,6
,,,,,50200070,7
,,,,,50200070,8
,,,,,50200070,9
,,,,,50200070,10
1,34,,,,50200071,1
,,7,19,210052,50200071,2
,,,65,190,50200071,3
,,,,,50200071,4
,,,,,50200071,5
,,,,,50200071,6
,,,,,50200071,7
,,,,,50200071,8
,,,,,50200071,9
1,29,,,,50200073,1
,,7,22,100060,50200073,2
,,,194,1985044,50200073,3
,,,,,50200073,4
,,,,,50200073,5
,,,,,50200073,6
,,,,,50200073,7
,,,,,50200073,8
,,,,,50200073,9
1,46,,,,50200074,1
,,7,89,76701,50200074,2
,,,185,343,50200074,3
,,,,,50200074,5
,,,,,50200074,6
,,,,,50200074,7
,,,,,50200074,8
1,34,,,,50200075,1
,,7,44,286291,50200075,2
,,,565,2681,50200075,3
,,,,,50200075,4
,,,,,50200075,5
,,,,,50200075,6
,,,,,50200075,7
,,,,,50200075,8
,,,,,50200075,9
2,35,,,,50200076,1
,,7,77,89467,50200076,2
,,,,,50200076,4
,,,,,50200076,5
,,,,,50200076,6
,,,,,50200076,8
,,,,,50200076,9
1,37,,,,50200079,1
,,7,131,140321,50200079,2
,,,,,50200079,12
1,42,,,,50200080,1
,,7,369,53954,50200080,2
,,,533,118,50200080,3
,,,,,50200080,4
,,,,,50200080,5
,,,,,50200080,6
,,,,,50200080,7
,,,,,50200080,8
2,34,,,,50200081,1
,,7,377,46767,50200081,2
,,,412,318,50200081,3
,,,,,50200081,4
,,,,,50200081,5
,,,,,50200081,6
,,,,,50200081,7
,,,,,50200081,8
2,29,,,,50200084,1
,,7,620,66629,50200084,2
,,,459,459,50200084,3
,,,,,50200084,4
,,,,,50200084,5
,,,,,50200084,6
,,,,,50200084,7
,,,,,50200084,8
1,33,,,,50200085,1
,,7,252,123790,50200085,2
,,,,,50200085,12
1,45,,,,50200086,1
,,7,5,934746,50200086,2
,,,37,1277,50200086,3
,,,,,50200086,4
,,,,,50200086,5
,,,,,50200086,6
,,,,,50200086,7
,,,,,50200086,8
2,33,,,,50200087,1
,,7,695,302,50200087,2
,,,727,,50200087,3
,,,,,50200087,4
,,,,,50200087,6
,,,,,50200087,7
,,,,,50200087,8
1,31,,,,50200088,1
,,7,135,237053,50200088,2
,,,193,214,50200088,3
,,,,,50200088,4
,,,,,50200088,5
,,,,,50200088,6
,,,,,50200088,7
,,,,,50200088,8
1,54,,,,50200089,1
,,7,390,12064,50200089,2
1,32,,,,50200090,1
,,7,368,70839,50200090,2
,,,601,604,50200090,3
,,,,,50200090,4
,,,,,50200090,6
,,,,,50200090,7
,,,,,50200090,8
2,43,,,,50200091,1
,,7,84,93644,50200091,2
2,39,,,,50200092,1
,,7,149,203466,50200092,2
,,,346,1094,50200092,3
,,,,,50200092,4
,,,,,50200092,5
2,30,,,,50200095,1
,,7,135,20804,50200095,2
,,,190,82,50200095,3
,,,,,50200095,4
,,,,,50200095,5
,,,,,50200095,6
,,,,,50200095,7
,,,,,50200095,8
2,38,,,,50200096,1
,,7,401,349,50200096,2
,,,401,349,50200096,3
,,,,,50200096,4
,,,,,50200096,5
,,,,,50200096,6
,,,,,50200096,7
,,,,,50200096,8
1,33,,,,50200097,1
,,7,140,1936482,50200097,2
,,,318,2236,50200097,3
,,,,,50200097,4
,,,,,50200097,5
,,,,,50200097,6
,,,,,50200097,7
,,,,,50200097,8
1,47,,,,50200098,1
,,7,360,31212,50200098,2
,,,,,50200098,12
1,36,,,,50200099,1
,,7,182,479712,50200099,2
,,,303,178,50200099,3
,,,,,50200099,5
,,,,,50200099,6
,,,,,50200099,7
,,,,,50200099,8
2,40,,,,50200100,1
,,7,18,53954,50200100,2
,,,59,40,50200100,3
,,,,,50200100,4
,,,,,50200100,5
,,,,,50200100,6
,,,,,50200100,7
2,23,,,,50200101,1
,,7,169,33732,50200101,2
,,,,,50200101,12
1,42,,,,50200102,1
,,7,21,572589,50200102,2
,,,,,50200102,5
,,,,,50200102,6
,,,,,50200102,7
,,,,,50200102,8
2,28,,,,50200103,1
,,7,485,34663,50200103,2
,,,375,63,50200103,3
,,,,,50200103,4
,,,,,50200103,5
,,,,,50200103,6
,,,,,50200103,7
2,28,,,,50200104,1
,,7,31,1348779,50200104,2
2,31,,,,50200106,1
,,7,317,113031,50200106,2
,,,374,320,50200106,3
,,,,,50200106,4
,,,,,50200106,5
,,,,,50200106,6
,,,,,50200106,7
,,,,,50200106,8
2,39,,,,50200107,1
,,7,100,1821977,50200107,2
,,,,,50200107,12
2,34,,,,50200108,1
,,7,108,56789,50200108,2
2,44,,,,50200109,1
,,7,200,54194,50200109,2
,,,259,143,50200109,3
,,,,,50200109,4
,,,,,50200109,5
,,,,,50200109,6
,,,,,50200109,7
,,,,,50200109,8
1,35,,,,50200110,1
,,7,163,3495,50200110,2
,,,227,40,50200110,3
,,,,,50200110,4
,,,,,50200110,5
,,,,,50200110,6
,,,,,50200110,7
1,35,,,,50200111,1
,,7,160,13957,50200111,2
,,,230,48,50200111,3
,,,,,50200111,4
,,,,,50200111,5
,,,,,50200111,6
1,31,,,,50200112,1
,,7,48,472372,50200112,2
,,,185,432,50200112,3
,,,,,50200112,6
1,45,,,,50200113,1
,,7,49,27966,50200113,2
1,47,,,,50200114,1
,,7,194,68018,50200114,2
,,,243,172,50200114,3
,,,,,50200114,4
,,,,,50200114,5
,,,,,50200114,6
1,38,,,,50200115,1
,,7,29,130933,50200115,2
,,,,,50200115,12
2,27,,,,50200116,1
,,7,122,33495,50200116,2
,,,121,80,50200116,3
,,,,,50200116,4
,,,,,50200116,5
,,,,,50200116,6
,,,,,50200116,7
1,39,,,,50200117,1
,,7,318,25984,50200117,2
,,,247,462,50200117,3
,,,,,50200117,4
,,,,,50200117,5
,,,,,50200117,6
,,,,,50200117,7
2,38,,,,50200118,1
,,7,89,1615482,50200118,2
,,,247,1376,50200118,3
,,,,,50200118,4
,,,,,50200118,5
1,30,,,,50200119,1
,,7,106,55662,50200119,2
,,,,,50200119,4
,,,,,50200119,5
,,,,,50200119,6
1,42,,,,50200120,1
,,7,10,192921,50200120,2
,,,116,1201,50200120,3
,,,,,50200120,4
,,,,,50200120,5
2,29,,,,50200121,1
,,7,55,573390,50200121,2
,,,228,2489,50200121,3
,,,,,50200121,4
,,,,,50200121,5
,,,,,50200121,6
,,,,,50200121,7
1,33,,,,50200122,1
,,7,181,2082516,50200122,2
,,,197,1030,50200122,3
,,,,,50200122,4
,,,,,50200122,5
1,36,,,,50200123,1
,,7,132,764272,50200123,2
,,,306,1234,50200123,3
,,,,,50200123,4
,,,,,50200123,5
,,,,,50200123,6
1,22,,,,50200125,1
,,7,181,46784,50200125,2
,,,,,50200125,12
1,43,,,,50200126,1
,,7,221,68934,50200126,2
1,43,,,,50200128,1
,,7,475,299868,50200128,2
,,,959,300,50200128,3
,,,,,50200128,4
1,49,,,,50200129,1
,,7,36,136289,50200129,2
2,47,,,,50200130,1
,,7,121,826,50200130,2
,,,197,41,50200130,3
,,,,,50200130,4
1,48,,,,50200132,1
,,7,200,640544,50200132,2
,,,259,570,50200132,3
,,,,,50200132,4
2,47,,,,50200134,1
,,7,288,17101,50200134,2
,,,384,200,50200134,3
,,,,,50200134,4
1,26,,,,50200135,1
,,7,84,97771,50200135,2
2,20,,,,50200136,1
,,7,375,292690,50200136,2
,,,732,818,50200136,3
,,,,,50200136,4
2,40,,,,50200137,1
,,7,52,12083,50200137,2
,,,69,63951,50200137,3
1,27,,,,50200138,1
,,7,294,19332,50200138,2
,,,,,50200138,
1,45,,,,50200139,1
,,7,88,806203,50200139,2
,,,383,,50200139,3
1,45,,,,50200140,1
,,7,117,278455,50200140,2
,,,142,1263,50200140,3
2,38,,,,50200142,1
,,7,38,33891,50200142,2
,,,87,88,50200142,3
1,49,,,,50200143,1
,,7,17,130809,50200143,2
1,20,,,,50200145,1
,,7,62,569445,50200145,2
1,48,,,,50200146,1
,,7,170,50619,50200146,2
,,,,,50200146,3
1,57,,,,50200147,1
,,7,147,181518,50200147,2
1,30,,,,50200148,1
,,7,234,2044,50200148,2
,,,318,46,50200148,3
1,39,,,,50200149,1
,,7,122,450632,50200149,2
2,54,,,,50200150,1
,,7,,,50200150,2
1,41,,,,50200151,1
,,7,355,61862,50200151,2
1,38,,,,50200153,1
,,7,19,407630,50200153,2
1,27,,,,50200154,1
,,7,68,494851,50200154,2
1,35,,,,50200155,1
,,7,4,105613,50200155,2
1,31,,,,50200156,1
,,7,19,1172335,50200156,2
2,31,,,,50200157,1
,,7,31,107872,50200157,2
2,34,,,,50200159,1
,,7,63,712341,50200159,2
2,28,,,,50200160,1
,,7,118,473977,50200160,2
1,55,,,,50200161,1
,,7,88,177082,50200161,2
;;;;

novinosrin · Posted 05-29-2018 07:55 PM

Getting the order right with an order by

proc sql;
create table want(drop=gender age) as
select *,max(gender) as _gender,max(age) as _age, max((visitno=2)*CD4RES) as CD4_bl,max((visitno=2)*VLRES) as vl_bl
from have
group by pid
order by pid,visitno;
quit;

View solution in original post

novinosrin · Posted 05-29-2018 07:49 PM

data WORK.HAVE;
  infile datalines dsd truncover;
  input GENDER:32. AGE:32. CULTURE:32. CD4RES:32. VLRES:32. pid:32. visitno:32.;
  label GENDER="GENDER" AGE="AGE" CULTURE="CULTURE" CD4RES="CD4RES" VLRES="VLRES";
datalines4;
2,48,,,,50200003,1
,,2,14,837504,50200003,2
,,,,,50200003,12
1,52,,,,50200005,1
,,7,112,119981,50200005,2
,,,276,229656000,50200005,3
,,,,,50200005,4
,,,,,50200005,5
,,,,,50200005,6
,,,,,50200005,7
,,,,,50200005,8
,,,,,50200005,9
,,,,,50200005,10
2,31,,,,50200006,1
,,7,66,204960000,50200006,2
,,,72,379880000,50200006,3
2,37,,,,50200007,1
,,7,17,251390000,50200007,2
,,,,,50200007,12
2,42,,,,50200008,1
,,7,51,679,50200008,2
,,,112,50,50200008,3
,,,,,50200008,4
,,,,,50200008,5
,,,,,50200008,6
,,,,,50200008,7
,,,,,50200008,8
,,,,,50200008,9
,,,,,50200008,10
1,36,,,,50200010,1
,,7,84,104957,50200010,2
,,,71,277,50200010,3
,,,,,50200010,4
,,,,,50200010,7
,,,,,50200010,8
,,,,,50200010,9
,,,,,50200010,10
1,47,,,,50200011,1
,,,147,232304,50200011,2
,,,250,789,50200011,3
,,,,,50200011,4
,,,,,50200011,5
,,,,,50200011,6
,,,,,50200011,7
,,,,,50200011,8
,,,,,50200011,9
,,,,,50200011,10
1,37,,,,50200012,1
,,7,222,176728000,50200012,2
,,,243,239,50200012,3
,,,,,50200012,4
,,,,,50200012,5
,,,,,50200012,6
,,,,,50200012,7
,,,,,50200012,8
,,,,,50200012,9
,,,,,50200012,10
2,35,,,,50200013,1
,,7,48,129591,50200013,2
,,,196,887,50200013,3
,,,,,50200013,4
,,,,,50200013,5
,,,,,50200013,6
,,,,,50200013,7
,,,,,50200013,8
,,,,,50200013,9
,,,,,50200013,10
2,31,,,,50200014,1
,,7,197,457600000,50200014,2
,,,141,6113,50200014,3
,,,,,50200014,4
,,,,,50200014,5
,,,,,50200014,6
,,,,,50200014,7
,,,,,50200014,8
,,,,,50200014,9
,,,,,50200014,10
1,36,,,,50200015,1
,,7,57,244357000,50200015,2
,,,,,50200015,12
2,24,,,,50200016,1
,,7,129,311039000,50200016,2
,,,208,315,50200016,3
,,,,,50200016,4
,,,,,50200016,5
,,,,,50200016,6
,,,,,50200016,7
,,,,,50200016,8
,,,,,50200016,9
,,,,,50200016,10
2,36,,,,50200017,1
,,7,135,886960000,50200017,2
,,,206,203,50200017,3
,,,,,50200017,5
,,,,,50200017,6
,,,,,50200017,7
,,,,,50200017,8
,,,,,50200017,9
,,,,,50200017,10
1,58,,,,50200018,1
,,7,94,451330000,50200018,2
,,,,,50200018,12
2,27,,,,50200020,1
,,7,59,532530000,50200020,2
,,,85,477,50200020,3
1,35,,,,50200021,1
,,7,162,17311,50200021,2
,,,,,50200021,4
,,,,,50200021,5
,,,,,50200021,6
,,,,,50200021,7
,,,,,50200021,8
,,,,,50200021,9
,,,,,50200021,10
2,30,,,,50200024,1
,,7,156,95684,50200024,2
,,,,,50200024,4
,,,,,50200024,5
,,,,,50200024,6
,,,,,50200024,7
,,,,,50200024,8
,,,,,50200024,9
,,,,,50200024,10
2,34,,,,50200025,1
,,7,47,788871,50200025,2
2,29,,,,50200026,1
,,7,137,275102,50200026,2
,,,28,637,50200026,3
,,,,,50200026,4
,,,,,50200026,5
,,,,,50200026,6
,,,,,50200026,7
,,,,,50200026,8
,,,,,50200026,9
,,,,,50200026,10
1,32,,,,50200027,1
,,7,188,161462,50200027,2
,,,299,324,50200027,3
,,,,,50200027,4
,,,,,50200027,5
,,,,,50200027,6
,,,,,50200027,7
,,,,,50200027,8
,,,,,50200027,9
,,,,,50200027,10
2,24,,,,50200028,1
,,7,36,390496,50200028,2
,,,264,375,50200028,3
,,,,,50200028,5
,,,,,50200028,6
,,,,,50200028,7
,,,,,50200028,8
,,,,,50200028,9
1,36,,,,50200029,1
,,7,48,571896,50200029,2
,,,,,50200029,12
2,40,,,,50200030,1
,,7,92,1535405,50200030,2
,,,,,50200030,5
,,,,,50200030,6
,,,,,50200030,7
,,,,,50200030,8
,,,,,50200030,9
,,,,,50200030,10
1,44,,,,50200031,1
,,7,46,3004999,50200031,2
,,,45,500,50200031,3
1,35,,,,50200033,1
,,7,29,167301,50200033,2
,,,61,578,50200033,3
2,34,,,,50200034,1
,,7,17,97496,50200034,2
,,,42,69202,50200034,3
,,,,,50200034,4
,,,,,50200034,5
,,,,,50200034,6
,,,,,50200034,7
,,,,,50200034,8
,,,,,50200034,9
,,,,,50200034,10
2,24,,,,50200035,1
,,7,70,225474,50200035,2
,,,139,487,50200035,3
,,,,,50200035,4
,,,,,50200035,5
,,,,,50200035,6
,,,,,50200035,7
,,,,,50200035,8
,,,,,50200035,9
,,,,,50200035,10
1,36,,,,50200036,1
,,7,270,911759,50200036,2
2,37,,,,50200037,1
,,7,23,153966,50200037,2
,,,120,309,50200037,3
,,,,,50200037,4
,,,,,50200037,6
,,,,,50200037,7
,,,,,50200037,8
,,,,,50200037,9
1,29,,,,50200038,1
,,7,75,139801,50200038,2
,,,171,280,50200038,3
1,52,,,,50200039,1
,,7,114,44825,50200039,2
,,,180,651,50200039,3
,,,,,50200039,4
,,,,,50200039,5
,,,,,50200039,6
,,,,,50200039,7
,,,,,50200039,8
,,,,,50200039,9
,,,,,50200039,10
1,37,,,,50200041,1
,,7,107,69383,50200041,2
,,,177,101156,50200041,3
,,,,,50200041,4
,,,,,50200041,5
,,,,,50200041,6
,,,,,50200041,7
,,,,,50200041,8
,,,,,50200041,9
,,,,,50200041,10
1,50,,,,50200042,1
,,7,,,50200042,2
2,38,,,,50200044,1
,,7,63,299125,50200044,2
,,,133,489,50200044,3
,,,,,50200044,4
,,,,,50200044,5
,,,,,50200044,6
,,,,,50200044,7
,,,,,50200044,8
2,25,,,,50200045,1
,,7,248,25243,50200045,2
,,,297,161,50200045,3
,,,,,50200045,5
,,,,,50200045,6
,,,,,50200045,7
,,,,,50200045,8
,,,,,50200045,9
,,,,,50200045,10
2,33,,,,50200048,1
,,7,209,93700,50200048,2
,,,,,50200048,4
,,,,,50200048,5
,,,,,50200048,6
,,,,,50200048,7
,,,,,50200048,8
,,,,,50200048,9
,,,,,50200048,10
1,47,,,,50200049,1
,,7,191,427300,50200049,2
,,,,,50200049,12
1,37,,,,50200051,1
,,7,180,1608104,50200051,2
,,,,,50200051,4
,,,,,50200051,5
,,,,,50200051,6
,,,,,50200051,7
,,,,,50200051,8
,,,,,50200051,9
,,,,,50200051,10
1,51,,,,50200052,1
,,7,84,637789,50200052,2
,,,148,336,50200052,3
,,,,,50200052,4
,,,,,50200052,5
,,,,,50200052,6
,,,,,50200052,7
,,,,,50200052,8
,,,,,50200052,9
,,,,,50200052,10
1,32,,,,50200053,1
,,7,200,160972,50200053,2
,,,180,4837,50200053,3
,,,,,50200053,4
,,,,,50200053,5
1,29,,,,50200054,1
,,7,220,237608,50200054,2
,,,206,159,50200054,3
,,,,,50200054,4
,,,,,50200054,5
,,,,,50200054,6
,,,,,50200054,7
,,,,,50200054,8
,,,,,50200054,9
,,,,,50200054,10
1,47,,,,50200059,1
,,7,164,973870,50200059,2
,,,643,1038,50200059,3
,,,,,50200059,4
,,,,,50200059,5
,,,,,50200059,6
,,,,,50200059,7
,,,,,50200059,8
,,,,,50200059,9
,,,,,50200059,10
2,40,,,,50200062,1
,,7,7,145945,50200062,2
,,,74,344,50200062,3
,,,,,50200062,4
,,,,,50200062,5
,,,,,50200062,6
,,,,,50200062,7
,,,,,50200062,8
,,,,,50200062,9
,,,,,50200062,10
1,49,,,,50200066,1
,,7,62,2455262,50200066,2
,,,141,176,50200066,3
,,,,,50200066,4
,,,,,50200066,5
,,,,,50200066,6
,,,,,50200066,7
,,,,,50200066,8
,,,,,50200066,9
,,,,,50200066,10
2,30,,,,50200067,1
,,7,50,225804,50200067,2
,,,,,50200067,12
1,48,,,,50200068,1
,,7,40,717818,50200068,2
,,,103,491,50200068,3
,,,,,50200068,4
,,,,,50200068,5
,,,,,50200068,6
,,,,,50200068,7
,,,,,50200068,8
,,,,,50200068,9
,,,,,50200068,10
1,38,,,,50200069,1
,,7,41,1016262,50200069,2
,,,,,50200069,3
,,,,,50200069,4
,,,,,50200069,7
1,47,,,,50200070,1
,,7,49,88521,50200070,2
,,,183,205,50200070,3
,,,,,50200070,4
,,,,,50200070,5
,,,,,50200070,6
,,,,,50200070,7
,,,,,50200070,8
,,,,,50200070,9
,,,,,50200070,10
1,34,,,,50200071,1
,,7,19,210052,50200071,2
,,,65,190,50200071,3
,,,,,50200071,4
,,,,,50200071,5
,,,,,50200071,6
,,,,,50200071,7
,,,,,50200071,8
,,,,,50200071,9
1,29,,,,50200073,1
,,7,22,100060,50200073,2
,,,194,1985044,50200073,3
,,,,,50200073,4
,,,,,50200073,5
,,,,,50200073,6
,,,,,50200073,7
,,,,,50200073,8
,,,,,50200073,9
1,46,,,,50200074,1
,,7,89,76701,50200074,2
,,,185,343,50200074,3
,,,,,50200074,5
,,,,,50200074,6
,,,,,50200074,7
,,,,,50200074,8
1,34,,,,50200075,1
,,7,44,286291,50200075,2
,,,565,2681,50200075,3
,,,,,50200075,4
,,,,,50200075,5
,,,,,50200075,6
,,,,,50200075,7
,,,,,50200075,8
,,,,,50200075,9
2,35,,,,50200076,1
,,7,77,89467,50200076,2
,,,,,50200076,4
,,,,,50200076,5
,,,,,50200076,6
,,,,,50200076,8
,,,,,50200076,9
1,37,,,,50200079,1
,,7,131,140321,50200079,2
,,,,,50200079,12
1,42,,,,50200080,1
,,7,369,53954,50200080,2
,,,533,118,50200080,3
,,,,,50200080,4
,,,,,50200080,5
,,,,,50200080,6
,,,,,50200080,7
,,,,,50200080,8
2,34,,,,50200081,1
,,7,377,46767,50200081,2
,,,412,318,50200081,3
,,,,,50200081,4
,,,,,50200081,5
,,,,,50200081,6
,,,,,50200081,7
,,,,,50200081,8
2,29,,,,50200084,1
,,7,620,66629,50200084,2
,,,459,459,50200084,3
,,,,,50200084,4
,,,,,50200084,5
,,,,,50200084,6
,,,,,50200084,7
,,,,,50200084,8
1,33,,,,50200085,1
,,7,252,123790,50200085,2
,,,,,50200085,12
1,45,,,,50200086,1
,,7,5,934746,50200086,2
,,,37,1277,50200086,3
,,,,,50200086,4
,,,,,50200086,5
,,,,,50200086,6
,,,,,50200086,7
,,,,,50200086,8
2,33,,,,50200087,1
,,7,695,302,50200087,2
,,,727,,50200087,3
,,,,,50200087,4
,,,,,50200087,6
,,,,,50200087,7
,,,,,50200087,8
1,31,,,,50200088,1
,,7,135,237053,50200088,2
,,,193,214,50200088,3
,,,,,50200088,4
,,,,,50200088,5
,,,,,50200088,6
,,,,,50200088,7
,,,,,50200088,8
1,54,,,,50200089,1
,,7,390,12064,50200089,2
1,32,,,,50200090,1
,,7,368,70839,50200090,2
,,,601,604,50200090,3
,,,,,50200090,4
,,,,,50200090,6
,,,,,50200090,7
,,,,,50200090,8
2,43,,,,50200091,1
,,7,84,93644,50200091,2
2,39,,,,50200092,1
,,7,149,203466,50200092,2
,,,346,1094,50200092,3
,,,,,50200092,4
,,,,,50200092,5
2,30,,,,50200095,1
,,7,135,20804,50200095,2
,,,190,82,50200095,3
,,,,,50200095,4
,,,,,50200095,5
,,,,,50200095,6
,,,,,50200095,7
,,,,,50200095,8
2,38,,,,50200096,1
,,7,401,349,50200096,2
,,,401,349,50200096,3
,,,,,50200096,4
,,,,,50200096,5
,,,,,50200096,6
,,,,,50200096,7
,,,,,50200096,8
1,33,,,,50200097,1
,,7,140,1936482,50200097,2
,,,318,2236,50200097,3
,,,,,50200097,4
,,,,,50200097,5
,,,,,50200097,6
,,,,,50200097,7
,,,,,50200097,8
1,47,,,,50200098,1
,,7,360,31212,50200098,2
,,,,,50200098,12
1,36,,,,50200099,1
,,7,182,479712,50200099,2
,,,303,178,50200099,3
,,,,,50200099,5
,,,,,50200099,6
,,,,,50200099,7
,,,,,50200099,8
2,40,,,,50200100,1
,,7,18,53954,50200100,2
,,,59,40,50200100,3
,,,,,50200100,4
,,,,,50200100,5
,,,,,50200100,6
,,,,,50200100,7
2,23,,,,50200101,1
,,7,169,33732,50200101,2
,,,,,50200101,12
1,42,,,,50200102,1
,,7,21,572589,50200102,2
,,,,,50200102,5
,,,,,50200102,6
,,,,,50200102,7
,,,,,50200102,8
2,28,,,,50200103,1
,,7,485,34663,50200103,2
,,,375,63,50200103,3
,,,,,50200103,4
,,,,,50200103,5
,,,,,50200103,6
,,,,,50200103,7
2,28,,,,50200104,1
,,7,31,1348779,50200104,2
2,31,,,,50200106,1
,,7,317,113031,50200106,2
,,,374,320,50200106,3
,,,,,50200106,4
,,,,,50200106,5
,,,,,50200106,6
,,,,,50200106,7
,,,,,50200106,8
2,39,,,,50200107,1
,,7,100,1821977,50200107,2
,,,,,50200107,12
2,34,,,,50200108,1
,,7,108,56789,50200108,2
2,44,,,,50200109,1
,,7,200,54194,50200109,2
,,,259,143,50200109,3
,,,,,50200109,4
,,,,,50200109,5
,,,,,50200109,6
,,,,,50200109,7
,,,,,50200109,8
1,35,,,,50200110,1
,,7,163,3495,50200110,2
,,,227,40,50200110,3
,,,,,50200110,4
,,,,,50200110,5
,,,,,50200110,6
,,,,,50200110,7
1,35,,,,50200111,1
,,7,160,13957,50200111,2
,,,230,48,50200111,3
,,,,,50200111,4
,,,,,50200111,5
,,,,,50200111,6
1,31,,,,50200112,1
,,7,48,472372,50200112,2
,,,185,432,50200112,3
,,,,,50200112,6
1,45,,,,50200113,1
,,7,49,27966,50200113,2
1,47,,,,50200114,1
,,7,194,68018,50200114,2
,,,243,172,50200114,3
,,,,,50200114,4
,,,,,50200114,5
,,,,,50200114,6
1,38,,,,50200115,1
,,7,29,130933,50200115,2
,,,,,50200115,12
2,27,,,,50200116,1
,,7,122,33495,50200116,2
,,,121,80,50200116,3
,,,,,50200116,4
,,,,,50200116,5
,,,,,50200116,6
,,,,,50200116,7
1,39,,,,50200117,1
,,7,318,25984,50200117,2
,,,247,462,50200117,3
,,,,,50200117,4
,,,,,50200117,5
,,,,,50200117,6
,,,,,50200117,7
2,38,,,,50200118,1
,,7,89,1615482,50200118,2
,,,247,1376,50200118,3
,,,,,50200118,4
,,,,,50200118,5
1,30,,,,50200119,1
,,7,106,55662,50200119,2
,,,,,50200119,4
,,,,,50200119,5
,,,,,50200119,6
1,42,,,,50200120,1
,,7,10,192921,50200120,2
,,,116,1201,50200120,3
,,,,,50200120,4
,,,,,50200120,5
2,29,,,,50200121,1
,,7,55,573390,50200121,2
,,,228,2489,50200121,3
,,,,,50200121,4
,,,,,50200121,5
,,,,,50200121,6
,,,,,50200121,7
1,33,,,,50200122,1
,,7,181,2082516,50200122,2
,,,197,1030,50200122,3
,,,,,50200122,4
,,,,,50200122,5
1,36,,,,50200123,1
,,7,132,764272,50200123,2
,,,306,1234,50200123,3
,,,,,50200123,4
,,,,,50200123,5
,,,,,50200123,6
1,22,,,,50200125,1
,,7,181,46784,50200125,2
,,,,,50200125,12
1,43,,,,50200126,1
,,7,221,68934,50200126,2
1,43,,,,50200128,1
,,7,475,299868,50200128,2
,,,959,300,50200128,3
,,,,,50200128,4
1,49,,,,50200129,1
,,7,36,136289,50200129,2
2,47,,,,50200130,1
,,7,121,826,50200130,2
,,,197,41,50200130,3
,,,,,50200130,4
1,48,,,,50200132,1
,,7,200,640544,50200132,2
,,,259,570,50200132,3
,,,,,50200132,4
2,47,,,,50200134,1
,,7,288,17101,50200134,2
,,,384,200,50200134,3
,,,,,50200134,4
1,26,,,,50200135,1
,,7,84,97771,50200135,2
2,20,,,,50200136,1
,,7,375,292690,50200136,2
,,,732,818,50200136,3
,,,,,50200136,4
2,40,,,,50200137,1
,,7,52,12083,50200137,2
,,,69,63951,50200137,3
1,27,,,,50200138,1
,,7,294,19332,50200138,2
,,,,,50200138,
1,45,,,,50200139,1
,,7,88,806203,50200139,2
,,,383,,50200139,3
1,45,,,,50200140,1
,,7,117,278455,50200140,2
,,,142,1263,50200140,3
2,38,,,,50200142,1
,,7,38,33891,50200142,2
,,,87,88,50200142,3
1,49,,,,50200143,1
,,7,17,130809,50200143,2
1,20,,,,50200145,1
,,7,62,569445,50200145,2
1,48,,,,50200146,1
,,7,170,50619,50200146,2
,,,,,50200146,3
1,57,,,,50200147,1
,,7,147,181518,50200147,2
1,30,,,,50200148,1
,,7,234,2044,50200148,2
,,,318,46,50200148,3
1,39,,,,50200149,1
,,7,122,450632,50200149,2
2,54,,,,50200150,1
,,7,,,50200150,2
1,41,,,,50200151,1
,,7,355,61862,50200151,2
1,38,,,,50200153,1
,,7,19,407630,50200153,2
1,27,,,,50200154,1
,,7,68,494851,50200154,2
1,35,,,,50200155,1
,,7,4,105613,50200155,2
1,31,,,,50200156,1
,,7,19,1172335,50200156,2
2,31,,,,50200157,1
,,7,31,107872,50200157,2
2,34,,,,50200159,1
,,7,63,712341,50200159,2
2,28,,,,50200160,1
,,7,118,473977,50200160,2
1,55,,,,50200161,1
,,7,88,177082,50200161,2
;;;;
proc sql;
create table want(drop=gender age) as
select *,max(gender) as _gender,max(age) as _age, max((visitno=2)*CD4RES) as CD4_bl,max((visitno=2)*VLRES) as vl_bl
from have
group by pid
order by pid;
quit;

novinosrin · Posted 05-29-2018 07:55 PM

Getting the order right with an order by

proc sql;
create table want(drop=gender age) as
select *,max(gender) as _gender,max(age) as _age, max((visitno=2)*CD4RES) as CD4_bl,max((visitno=2)*VLRES) as vl_bl
from have
group by pid
order by pid,visitno;
quit;

jpsmith · Posted 05-29-2018 08:00 PM

Thank you! I was just about to mention that it needed a proc sort, but you accomplished this in proc sql faster than I could type. I want ahead with the proc sort to rename the age and gender variables, but thats just a cosmetic thing: proc sort data=want (rename=(_age=age _gender=gender)); by pid visitno; run;).

Thanks so much for your quick and incredibly helpful response!

Jonathan

novinosrin · Posted 05-29-2018 08:12 PM

You can rename in the same step using the dataset option. I was lazy and i assumed you sure can. But anyway,

proc sql;
create table want(drop=gender age rename=(_age=age _gender=gender)) as
select *,max(gender) as _gender,max(age) as _age, max((visitno=2)*CD4RES) as CD4_bl,max((visitno=2)*VLRES) as vl_bl
from have
group by pid
order by pid,visitno;
quit;

jpsmith · Posted 05-29-2018 08:14 PM

You're the best - thanks for convincing me to dive deeper into PROC SQL

jpsmith · Posted 05-29-2018 08:23 PM

Do you have any advice for date and character variables that would appear in the second visit that need to be carried over as well? For instance, if baseline date was 1/1/2018 and a variable "symptoms" was "yes" or "no"?

novinosrin · Posted 05-29-2018 10:32 PM

@jpsmith Sorry i was on my way back home and didn't see your additional requirement. I'm afraid I can only look into it when i get to my college tomorrow( Chicago time 9:30AM). I don't have SAS software at home.

In the mean time, if you could provide me a sample of your additional requirement, it can only help in testing effectively. Thanks and have a good night.

Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

Re: Drawing data from second visit to carry over for all patient data

SAS Innovate 2025: Register Now