SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

[ Edited ]

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      
PIDVISITNOGENDERAGECULTURECD4RESVLRES
10011123   
10012  150010000
10013  155012000
10021143   
10022  1425120000
10023  1400134200
10024  0550132110
100310670  
10032  190090000
10033  1800150000
10034  085013000

 

I want it to look like this:

Data: Want        
PIDVISITNOGENDERAGECULTURECD4RESVLRESCD4_blvl_bl
10011123   50010000
1001212315001000050010000
1001312305501200050010000
10021143   425120000
100221431425120000425120000
100231431400134200425120000
100241430550132110425120000
100310670  90090000
1003206719009000090090000
10033067 180015000090090000
10034067 08501300090090000

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
;;;;

 


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 1,577

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

Posted in reply to novinosrin

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


All Replies
PROC Star
Posts: 1,577

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

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;
Solution
3 weeks ago
PROC Star
Posts: 1,577

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

Posted in reply to novinosrin

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;
Occasional Contributor
Posts: 14

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

Posted in reply to novinosrin

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; runSmiley Wink.

 

Thanks so much for your quick and incredibly helpful response!

Jonathan

PROC Star
Posts: 1,577

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

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;
Occasional Contributor
Posts: 14

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

Posted in reply to novinosrin

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

Occasional Contributor
Posts: 14

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

Posted in reply to novinosrin

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"? 

PROC Star
Posts: 1,577

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

[ Edited ]

@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.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 141 views
  • 1 like
  • 2 in conversation