Hi all, I have the following situation. I have two data sets (DS1 and DS2) that I want to use as a source to create a third data set (DS3). In short, the third data set (DS3) will be DS1 with an extra variable as the sum of variable X. To get the sum, you use the dates in DS1 and then sum any X variable between the previous date and current date defined in DS1. For example for A1 Visit 1, sum(X)={sum of X where date<=07/01/14}; for A1 Visit 2, sum(X)={sum of X where 07/01/14<date<=08/02/14}; etc. This code would have have to work across different subjects. I tried to use merge, first.Subject last.Subject, etc but this one stumps me. I'm not sure if a hash table would work here. I am not proficient in proc sql so if there is a data step option would be preferred but an sql statement will do as well. Thank you for your help! DS1 Subject Visit Date A1 1 07/01/14 A1 2 08/02/14 A1 3 08/15/14 A2 1 05/14/15 A2 2 07/22/15 data ds1;     input subid $ visit date mmddyy8.;     datalines; A1 1 07/01/14 A1 2 08/02/14 A1 3 08/15/14 A2 1 05/14/15 A2 2 07/22/15 ; DS2 Subject Date X A1 06/02/14 3 A1 07/01/14 0 A1 08/02/14 1 A1 08/10/14 10 A1 08/15/14 2 A2 05/14/15 2 A2 05/23/15 3 A2 06/01/15 0 A2 07/22/15 7 data ds2;     input subid $ date mmddyy8. x;     datalines; A1 06/02/14 3 A1 07/01/14 0 A1 08/02/14 1 A1 08/10/14 10 A1 08/15/14 2 A2 05/14/15 2 A2 05/23/15 3 A2 06/01/15 0 A2 07/22/15 7 ; DS3 Subject Visit Date Sum(X) A1 1 07/01/14 3+0=3 A1 2 08/02/14 1 A1 3 08/15/14 10+2=12 A2 1 05/14/15 2 A2 2 07/22/15 3+0+7=10 ... View more

Hello all, I am trying to macro-tize a portion of my code (in red) that involves the eta variable. %let &Nsites=5; data PHData;     array SiteDummy{&Nsites} (&Nsites*0);     array SiteRate{&Nsites} (&Nsites*0);      eta=log(med/3)*StomaType +           log(med/SiteRate{2})*SiteDummy{2} +           log(med/SiteRate{3})*SiteDummy{3} +           log(med/SiteRate{4})*SiteDummy{4} +           log(med/SiteRate{5})*SiteDummy{5} +           log(med/7)*barrier; run; Given the number of sites, that portion of code should be dynamic. For example if &Nsites=1 then it should be "log(med/SiteRate{2})*SiteDummy{2} +"                    if &Nsites=2 then it should be "log(med/SiteRate{2})*SiteDummy{2} +                                                                 log(med/SiteRate{3})*SiteDummy{3} +" Can a macro be written so it can be used in the following way: %let &Nsites=5; data PHData;     array SiteDummy{&Nsites} (&Nsites*0);     array SiteRate{&Nsites} (&Nsites*0); eta=log(med/3)*StomaType +     %SiteLinPred(Nsites=&Nsites)      log(med/7)*barrier; run; Thank you ... View more

Hello I have the following dataset format: R1 R2 R3 R4 R5 A B C D E F G H I J K L M N O P Q R S T ... ... ... ... ... and I would like to create 4 new datasets photo1, photo2, photo3, photo4, etc: R1 R2 R3 R4 R5 A B C D E R1 R2 R3 R4 R5 F G H I J etc. I am not sure if this can be done using a BY statement some how or maybe through a proc append, etc. Any suggestions? ... View more

Hello my current dataset is in the form below (dummy data): ID Visit Group1 ScoreX Group2 ScoreY Group3 ScoreZ 1 1 A 0 B 0 C 0 1 2 A 0 B 1 C 0 1 3 A 1 B 1 C 0 1 4 A 2 B 1 C 0 2 1 C 1 A 0 B 0 2 2 C 0 A 1 B 1 2 3 C 0 A 3 B 2 2 4 C 3 A 3 B 3 and would like to convert into two ways: 1) i would like to go from wide to long format ***UPDATE part 1 solved: see post 3 below*** ID Group Visit Score 1 A 1 0 1 A 2 0 1 A 3 1 1 A 4 2 1 B 1 0 1 B 2 1 1 B 3 1 1 B 4 1 1 C 1 0 1 C 2 0 1 C 3 0 1 C 4 0 2 B 1 0 2 B 2 0 2 B 3 0 2 B 4 3 2 A 1 0 2 A 2 1 ... (continued) ... ... and 2) I would like to distill the data into this below. ***UPDATE part 2 solved: see post 4 and 5 below*** Essentially I am counting the number of visits it took to get a score of 1, 2, or 3. ID Group Visit of first 1 score Visit of first 2 score Visit of first 3 score 1 A 3 4 . 1 B 2 . . 1 C . . . 2 A 2 . 3 2 B 2 3 4 2 C 1 . 4 I believe I can do this with a combination of arrays but I am not sure how to handle the groups when my data is in wide format (1st table). So my thoughts are first I need to get it into long format (2nd table) and then compress the data (3rd table). I do not know SQL so any datastep programming help would be greatly appreciated ... View more

Hello all, here is what I have Year VarValue 2000 3 2000 5 2000 2000 10 2001 7 2001 2 2002 2002 9 2002 2004 2 2004 I would like to impute the missing values of VarValue for each year with the minimum VarValue for that year. Something like this below. A datastep would be preferable as I do not know SQL (but I am not opposed to SQL as long as I can modify it easily). Thanks!! Year VarValue 2000 3 2000 5 2000 3 2000 10 2001 7 2001 2 2002 9 2002 9 2002 9 2004 2 2004 2 ... View more

Hello all, I have the following data format. Each subject could be rated in either a Y or R category. Subject Rater Categorization 1 1 Y 1 2 Y 1 3 R 2 1 R 2 2 R 3 1 Y 3 2 Y 4 1 Y 4 2 4 3 R I would like to aggregate the data to the following count format: Subject Cat_Y Cat_R 1 2 1 2 0 2 3 2 0 4 1 1 I am not sure I the best way would be to do this via a datastep or some proc (e.g means, freq, etc). In total I have ratings for about 60 subjects so I would like to automate the process as much as possible. Any suggestions? Thanks! ... View more