data vtemp / view=vtemp;   set have;   if course_id='Eng' then keepdup=_n_; run;   proc sort data=vtemp out=want (drop=keepdup) nodupkey  DUPOUT= have_DUPDEL ;   by student_id course_id  keepdup; run; ... View more

A minor tweak (simple, but improves efficiency), or  a major one (more complex but a lot more efficiency in the case of large data sets).     Minor (don't bother reading the last 30% of the file):    data want;     set have nobs=_nobs_;     if  _n_ > 0.45*_nobs_;     if _n_ > 0.70 * _nobs_ then stop; run;   The above actually reads (and throws away) the first unwanted 45% of the file, but at least stops once the 70% obs has been reached.   Major tweak. But if your file is big, you might want to avoid reading the first 45%, just to throw it away.  You can use the data set parameter FIRSTOBS to tell sas to skip the first 45% (and the OBS parameter to tell it to stop at 70%).  To do that ask dictionary.tables for the number of observations, and use macro expressions to calculate FIRSTOBS and OBS:   proc sql;   select nobs into :nrecs from dictionary.tables where libname='WORK' and memname='HAVE'; quit; %let fobs=%sysfunc(floor(%sysevalf(0.45*&nrecs))); %let obs=%sysfunc(ceil(%sysevalf(0.7*&nrecs)));   data want;   set have (firstobs = &fobs   obs=&ob ); run;   regards, Mark   ... View more

Why are these records in your WANT dataset?  They don't fit your description as I understand it. 102 05AUG2013:23:00 .         ** This record is NOT more than 6 hours from its predecessor (102 05AUG2013:19:04 .). 107 28AUG2013:14:00 .         ** Same issue (preceding record is 107 28AUG2013:13:40 .). ... View more

For each company in a decile/industry group, you want CMED, the "complimentary median" (i.e. median of group members except the company in hand) of variable X. But of course, you can't directly calculate the CMED the same way you could directly calculate the complimentary mean - after all its a non-parametric measure. But you are helped by the fact that you're going to get no more than 3 distinct values for CMED over each group.  Start out with the MED(X), the "ordinary median" (i.e. including the subject company). Then all companies with X <  MED(X) will have the same CMED (i.e. dropping any of these companies from median calculation is the same as dropping the minimum X).  Call this value MEDexMIN(X). The same principle applies to all companies with X> MED(X).  Dropping any of them will produce the same CMED as dropping the maximum X.  Call it MEDexMAX(X). And if the company has X=MED(X)?  Then the complimentary median is the mid-point between MEDexMIN(X) and MEDexMAX(X). Note all of these could have the same value if there are a lot of ties at MED(X).. Here's a suggested program - I'd consider it a good example of why and how to use the double DOW approach.  It assumes your data set HAVE is grouped by decile and industry, and that you have (say) no more than 100 companies in a single decile/industry group: data want (drop=_:);   array _x {100}; /* Establishes variables _x1 ... _x100 */   do _I = 1 by 1 until (last.industry);     set have;     by decile industry nosorted;     _x{_I}=x;     _xmax=max(_xmax,x);  if _xmax=x then _imax=_I;     _xmin=min(_xmin,x);  if _xmin=x then _imin=_I;   end;   _med=median(of _x:);   _x{_Imax}=.;  _MEDexMAX=median(of _x:);  _x{_imax}=_xmax;   _x{_imin}=.;  _MEDexMIN=median(of _x:);  _x{_imin}=_xmin;   do until (last.industry);     set have;     by decile industry notsorted;     if x=. then CMED=.;     else select (sign(x-_med));       when (1)  cmed = MEDexMAX;       when (-1) cmed = MEDexMIN;       when (0)  cmed = (MEDexMAX+MEDexMIN)/2;     end;     output;   end; run; ... View more

Art: I don't know that it is really a lot less typing to apply the do loop, as long as there is a way to avoid typing the list of varible names.  One way is the sql way you suggest. Another is to use Tom's solution, but instead of listing the variables, use the keywords _numeric_ and _character_  as in; data want (keep=name value);   set have;   array nam {*} $32 _character_;   array val{*}           _numeric_ ;   do I=1 to dim(nam);     name=nam{I};     value=val{I};     output;   end; run; For folks unfamiliar with the "select ... into"  and dictionary.xxx techniques, the array approach is probably more intuitive. BUT the downside ... while this technique free of requiring a naming converntion for the variable names  (i.e. you don't need varnames of xxx and xxx_value) , it absolutely depends on the order of the "name" vars to correspond with the order of the numeric value variables. regards, Mark ... View more

Why would you ever have a)  "if date_variable>=input('2013-07-26',yymmdd10.);" instead of b) "if date_variable>='26jul2013'd;" Both versions assume that date_variable is (1) numeric, and (2) contains a date value.  It matters not what format or informat has been assigned to the variable.  And version (a) will ask SAS to needlessly run the INPUT function NOBS time, always getting the same result, namely '26jul2013'd for comparison purposes.  Version b has no such overhead. Mark ... View more

- On DI studio, please see http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0808A&L=sas-l&P=R456&D=0&H=0&O=T&T=1&m=268812 And also Paul Dorfmans' excellent reflections of DI Studio on SAS-L at http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1109b&L=sas-l&F=&S=&P=8121 But back to your question.  Below is some code that might give you a fighting chance.  It reads the specified table and writes the SQL code to the log.  Shouldn't be too hard to convert that to macro construction: data have;    input @1 label1 $ @3 label2 $ @5 label3 $1. @7 condition $; datalines; a d p code1 a d p code7 a e q code2 a e q code3 b p   code4 b a r code5 d a r code6 run; data _null_;    set have end=end_of_have;    by label1 label2 label3 condition notsorted;    L1=quote(trim(label1));    L2=quote(trim(label2));    L3=quote(trim(label3));    Lc=quote(trim(condition));    if _n_=1 then put 'proc sql;'        / 'create table op as select label1, label2, label3, condition,'        / 'case label1';    if      first.label1 then put        @3 'when ' L1 'then case label2'           / @5 'when ' L2 'then case label3'              / @7 'when ' L3 'then case condition';    else if first.label2 then put             @5 'when ' L2 'then case label3'             / @7 'when ' L3 'then case condition';    else if first.label3 then put               @7 'when ' L3 'then case condition';    put           @9 'when ' LC 'then "y"';    if last.label3 then put               @7 'else "n" end';    if last.label2 then put             @5 'else "n" end';    if last.label1 then put           @3 'else "n" end';    if end_of_have then put 'else "n" end;' / 'quit;'; run; Message was edited by: Mark Keintz  - 2nd try at pasting script properly ... View more

So this is a homework?  Not a job assignment? ... View more