Hello everybody, I'm trying to select observations from one dataset based on certain criteria from another dataset in proc sql. I'm using WHERE EXISTS statement to filter-out. My question, is there a way to include a variable (or more) from inside the WHERE EXISTS subquery into the main query? Here is an example. I'm selecting observations from have1 if visitDt was before or equals to trtDt in have2. The query runs well, but once I request any variable from the subquery (e.g.   b.trtDt), the program will err. I was wondering if there a way to modify the code to have variable(s) from the subquery appear in the main query results. data have1; input id visit visitDate mmddyy10.; format visitDate mmddyy10.; cards; 1 1 01/01/2012 1 2 02/01/2012 1 3 03/01/2012 1 4 04/01/2012 1 5 05/01/2012 2 1 01/01/2012 2 2 02/01/2012 2 3 03/01/2012 2 4 04/01/2012 2 5 05/01/2012 ; run; data have2; input id trtDt mmddyy10.; format trtDt mmddyy10.; cards; 1 02/01/2012 2 04/01/2012 ; run; proc sql;   select id, visit   from have1 a   where exists      (select * from have2  b                            where visitDate <= trtDt                                                         and a.id=b.id );     quit; Thanks for help! ... View more

Hi All, Is there a way in PROC REPORT to have more than one variable in rows. This can be easily done with proc tabulate, but I'm not sure for report procedure... For example, I'm interested in the following, with variables Age, gender, treatment which are categorical variables, and some other continuous variable that I'm calculating mean for... these are just examples, if doable I'm interested in adding many other categorical variables in rows.           Treatment A          Treatment B           N     Mean             N Mean Age <65     50     60               90     55 >=65   40     70               100     90 Gender F          60     55               100     45 M          30     60               90      60 thanks for help ... View more

I have multiple date variables (date1, date2, ..., date8) that I am taking the earliest of them (min (of date1 - date8)) and using for censoring in a time-to-event analysis. Some dates could be the same; others may be missing. I'd like to output which date(s) were the minimum, thus used for censoring and output VNAME. Is there a way, by arrays maybe to output a one column that tells which of these dates equaled the minimum? for example: date1     date2     date3     date4     date5     ...     minDate 1/1/12     .           5/7/13     .          .                          date1 .            6/5/12    .             6/5/12       .                     date2/date4 and so on ... Thanks ... View more

I'm using proc transpose in a macro (part of it below); and I have ID statement included; but sometimes I don't want to pass the macro variable &ID to the macro; if I don't pass the argument for ID, SAS will give error. However, SAS will not give an error when the &Where for WHERE statement is not specified in the macro... Is there a way to tell SAS not to err when a column for ID statement is not specified in macro parameters? the error msg given by SAS for not specifying a column name for the ID statement is " LINE and COLUMN cannot be determined. NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error               has occurred. ERROR 22-322: Syntax error, expecting one of the following: a name, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. ERROR: Variable NAME not found. " %macro WideTranspose (data=, out= ,by=, id=,where= ,list=, Prefixlist=); . . .   %sort( &data, &BY);   proc transpose data=&data out=out&n (drop= _:) prefix=&prefix LET;   by &by;   var &list;   id &id;   where &where;   run; . %mend; %WideTranspose (data=data,   out=out ,   by=var1 var2,   id= ,   where= ,   list=var3 var4,   Prefixlist=var3pre var4pre); Thanks ... View more

Hi All, Is there a simple way (datastep or sql) to associate multiple observations in a dataset with specific time points to an earlier time point in a one-per-id dataset, as long as the observation falls inbetween time point of interest? an example may explain it better... I have a dataset with visits dates; and I have treatments with treatment dates. I'd like to associate each trt to the visit earlier as long as the trt was given in the period between the two consecutive visits... data visits; input visit $ visitdt mmddyy8.; format visitdt mmddyy8.; cards; 1 11/12/12 2 03/02/13 3 04/15/13 4 06/16/13 ; run; data trt; input trt $ trtDt mmddyy8.; format trtDt mmddyy8.; cards; A 11/20/12 A 11/29/12 B 12/02/12 A 03/01/13 B 04/15/13 A 04/16/13 B 06/17/13 A 08/15/13 ; run; I'm interested to this structure... Visit       visitdt                    trt           trtDt 1              11/12/12              A             11/20/12 1              11/12/12              A             11/29/12 1              11/12/12              B             12/02/12 1              11/12/12              A             03/01/13 2              03/02/13                              . 3              04/15/13              B             04/15/13 3              04/15/13              A             04/16/13 4              06/16/13              B             06/17/13 4              06/16/13              A             08/15/13 Any help is appreciated... thanks! ... View more

Hello Community, Is there any way in PROC REPORT to apply alternate shading of ALL rows of a group variable in ODS RTF? Something like the attached table... I'm using this mock dataset to illustrate.... ods rtf file='C:\******.rtf' startpage=never BODYTITLE style=journal; data have;   do month = 'Jan','Feb','Mar','Apr';   do dept= 'Assistants','IT','Dev';   do N= 1 to 10;output;   do salary=ranuni(737346) * 100000;output;   end;   end;   end;   end; run; proc report data=have nowd headline;   column month dept salary;   define month/group width=7;   define dept/group;   define salary/ max; run; ods rtf close; ... View more