cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jpsmith
Fluorite | Level 6 jpsmith
Fluorite | Level 6
Go to Solution

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

Posted 05-29-2018 07:27 PM (907 views)

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

 

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

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

Posted 05-29-2018 07:55 PM (1026 views)  |   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

0 Likes
7 REPLIES 7
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

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

Posted 05-29-2018 07:49 PM (892 views)  |   In reply to jpsmith 
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;
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

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

Posted 05-29-2018 07:55 PM (1027 views)  |   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;
0 Likes
jpsmith
Fluorite | Level 6 jpsmith
Fluorite | Level 6

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

Posted 05-29-2018 08:00 PM (878 views)  |   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; run;).

 

Thanks so much for your quick and incredibly helpful response!

Jonathan

novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

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

Posted 05-29-2018 08:12 PM (868 views)  |   In reply to jpsmith

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;
0 Likes
jpsmith
Fluorite | Level 6 jpsmith
Fluorite | Level 6

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

Posted 05-29-2018 08:14 PM (866 views)  |   In reply to novinosrin

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

0 Likes
jpsmith
Fluorite | Level 6 jpsmith
Fluorite | Level 6

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

Posted 05-29-2018 08:23 PM (863 views)  |   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"? 

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

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

Posted 05-29-2018 10:32 PM (843 views)  |   In reply to jpsmith

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

0 Likes

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Watch How to connect to databases in SAS Viya on YouTube

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • ‎05-29-2018 07:27 PM
  • 908 views
  • 1 like
  • 2 in conversation