Sorry about that Reeza...I closed one. ... View more

Hi All,                I need some help..I have some sample data below: First Scenario: If the result is missing I need to look back to get the result from the previous visit within each test. So for example subjid 1 for test ABC has missing result for DAY3 and WEEK1 then we get the result from DAY2 for both DAY3 and WEEK1. So basically we just have to keep looking back all the visits until we get the result. I did obtain the results by using Proc sql coalesce  when I had few visits but now I have like 80 visits and I cant program it using sql coalesce. I am looking for a macro to obtain the result.   Second Scenario: The looking back of visits can be from another study too...as different studies are combined here. If suppose for a subject, result of one of the visit is missing from study XYZ(which is third study) then we go back to the study RWA(second study) to look for the result and if its still missing then we look into ABC(first study) to get the results. I don't have an example of sample data here.    For example,    DATA HAVE; infile datalines dlm=','; INPUT SUBJID $ TEST $ TESTCD VISIT $ VISITN RESULT ; DATALINES; 1,ABC,1,DAY1,1,12345 1,ABC,1,DAY2,2,32457 1,ABC,1,DAY3,3,. 1,ABC,1,WEEK1,4,. 1,ABC,1,WEEK2,5,13845 1,ABC,1,WEEK3,6,. 1,ABC,1,WEEK4,7,. 1,ABC,1,EW,8,45890 1,DEF,2,DAY1,1,145 1,DEF,2,DAY2,2,. 1,DEF,2,DAY3,3,13085 1,DEF,2,WEEK1,4,. 1,DEF,2,WEEK2,5,1.45 1,DEF,2,WEEK3,6,12.34 1,DEF,2,WEEK4,7,0 1,DEF,2,EW,8,. ; RUN;   Any help would be greatly appreciated. Thanks... ... View more

Hi Scott, Thanks for the reply but these are not temporary files. We load the data file every now and then with same names so I need to add a sequence number to them. ... View more

Hi ballard,                    Sorry, I guess without an example that code was not of much use. Please have a look at this. I provided a screenshot of Ex_2 dataset .   data example; input id visit$ visit_num var1 var2 var3; datalines; 1 Visit1 1 20 30 35 1 Visit2 2 75 35 45 2 Visit1 3 99 15 34 2 Visit2 4 55 38 48 ; run; data ex_2; set example; by visit_num visit ; if not(40<var1<70) then t1= catx(' ',"Score1",cats('(',var1,')')); if not(40<var2<70) then t2= catx(' ',"Score2",cats('(',var2,')')); if not(40<var3<70) then t3= catx(' ',"Score3",cats('(',var3,')')); total = catx(' ',t1,t2,t3); drop t1 t2 t3; run;   So now my question is, as you can see in the screenshot the highlighted row met both the conditions for Score1 (75) Score2 (35) at Visit2 for ID 1 so how can I code so that I get the output as below. Something like different score's that meet the conditions into different observation for a subject at that visit.Thanks in advance for the help.   ID VISIT VISIT_NUM TOTAL 1  Visit2   2                   Score1(75) 1  Visit2   2                   Score2(35)   ... View more

Sorry about the typo error of < 70 its actually >70 and there are double quotes in my data...its just while typing here to provide example I typed wrong.. ... View more

Hi ballard,                  I tried using select when also.. it did not work. I got the results using a lot of if then else statements and I am using 3 variables here but I have  several variables in my data..so I was looking for an alternate effective method. Please have a look at what I have used. As I have used different combinations of variables to check the condition.   if ( (var1<70 and var1<40) and var1> .) and ( (var2<70 and var2<40) and var2> .) and ( (var3<70 and var3<40) and var3> .) then Total = ('Score1" || " (" || strip(var1) || ")" )|| ( "Score2" || " (" || strip(var2) || ")" || ( "Score3" || " (" || strip(var3) || ")"  ; /***here I concatenated the score's in one single variable and I got the result like total=Score1(45) Score2(20) Score3(38) but I want to see all 3 score's if they exist for a ID in different rows as below   OBS   ID      TOTAL 1)  Subject1 SCORE1(45) 2) Subject1  SCORE2(20) 3) Subject1  SCORE3(38)  ***/   Else if ( (var1<70 and var1<40) and var1> .) and ( (var2<70 and var2<40) and var2> .) then total = ("Score1" || " (" || strip(var1) || ")")|| ("Score2" || " (" || strip(var2) || ")" ;   Else if ( (var1<70 and var1<40) and var1> .) and ( (var3<70 and var3<40) and var3> .) then Total = ('Score1" || " (" || strip(var1) || ")" )|| (' Score3" || " (" || strip(var3) || ")"  ) ; Else if ( (var2<70 and var2<40) and var2> .) and ( (var3<70 and var3<40) and var3> .) then Total = ( "Score2" || " (" || strip(var2) || ")") || (" Score3" || " (" || strip(var3) || ")"  ) ; Else if ( (var1<70 and var1<40) and var1> .) then Total = " Score1" || " (" || strip(var1) || ")" ; Else if ( (var2<70 and var2<40) and var2> .) then Total = "Score2" || " (" || strip(var2) || ")"  ;   Else if ( (var3<70 and var3<40) and var3> .) then Total =  " Score3" || " (" || strip(var3) || ")" ;   ... View more

And also I would like to add something here...which I missed the visits for baseline are 'Period1_day-1' 'Period2_day-2' and not just Period1' 'Period2' . ... View more

 I am merging them by tests..                data data4; merge baseline data3; by tests ; run;          If I use visit in the by statement then I am getting the aval_mean value just for the the period1 and period2 visits and missing for postbaseline visits... ... View more