Here is an approach: First, some test data: data have; do firm=1 to 10; do year=2009 to 2019; do month=1 to 12; date=mdy(month,1,year); returns=round(ranuni(414)*2-0.6,0.01); output; end; end; end; format date yymmdd10.; keep firm date returns; run; Then calculate the year: data have_y; set have; year=year(date); run; For the final step, the data must sorted by firm and year: data want; array year_returns(12) 8 _temporary_; do _N_=1 by 1 until(last.year); set have_y; by firm year; year_returns(_N_)=returns; end; avg_return=mean(of year_returns(*)); do _N_=1 to dim(year_returns); if year_returns(_N_)>=avg_return then call missing(year_returns(_N_)); end; semi_variance=var(of year_returns(*)); keep firm year avg_return semi_variance; run; The idea is very simple: put the returns in an array, calculate the average. Blank out (call missing) those that are average or above. Calculate the variance of the rest. ... View more

Depends on what you want to accomplish. I think that the SAS datastep compiler is reasonably optimized for this kind of processing, meaning that your original statement is probably as efficient as it gets in terms of performance. I think the program will stop checking the following parts as soon as one part of a multiple OR clause has been proven true.    So, if you want fast and dirty, use the construct if min(of x1-x5)<10 or max(of y1-y5)>10 then... By "fast and dirty" I mean that this is fast to write, but harder to read than the original statement, and it probably executes a teeny bit slower, as the program will first compare all the variables in each part to each other to find the minimum or maximum, and then compare the result to 10.   If you have a really large number of variables, and you really care about execution time, I would recommend using a macro construct. One possibility, if you have two groups of variables, prefixed by "X" and "Y", is to use SQL to construct the clauses: Proc sql noprint; select cats(name,'<10') into :x_clause separated by ' or ' from dictionary.tables where memname='<data set name in caps>' and libname='<library name in caps>' and name like 'X%'; select cats(name,'>10') into :y_clause separated by ' or ' from dictionary.tables where memname='<data set name in caps>' and libname='<library name in caps>' and name like 'Y%'; quit; You can then write you IF statement as if &x_clause or &y_clause then... But in most cases I would go with the original statement, or the MIN and MAX construct, as others have also suggested. The macro option certainly beats entering 100 variable names by hand, and if you have that many variables the performance improvement may be worthwhile.  ... View more

Two problems with your code: You used &i instead of &s in the %SCAN call The first macro parameter is unnecessary So, I think that hat you want to do is something like this: proc sql; select distinct sex into:gen separated by "~" from sashelp.class; quit; options symbolgen mprint; %put &gen; %macro prnt(i,upto); %local s var; %do s=&i %to &upto; %let var=%scan(&gen,&s,'~'); Proc sql; create table newt_&var as select * from sashelp.class where sex="&var"; quit; %end; %mend; %prnt (1,2); ... View more

I think something lke this may work: /* Get the unique exception codes and store them in macro variable exceptions */ %local Exceptions Exception i; proc sql noprint; select distinct EXCEPTION_CD into :exceptions separated by ' ' from REWARDS.REGISTRATION where not missing(EXCEPTION_CD) order by 1 ; quit; %put Exceptions in file: &exceptions; /* Delete macro symbols from previous run, if any, there can be up to 99 exception codes */ %do i=1 %to 99; %if %symexist(cnd_cd_exc&i) %then %symdel cnd_cd_exc&i; %if %symexist(cnd_grp_cd_exc&i) %then %symdel cnd_grp_cd_exc&i; %if %symexist(exc_type&i) %then %symdel exc_type&i; %end; /* Loop through the number of exceptions and stored the candidate codes, candidate group codes and exception types associated to each exception code in macro variables */ %do i = 1 %to &sqlobs; %let exception=%scan(&exceptions,&i); %global cnd_cd_exc&exception cnd_grp_cd_exc&exception exc_type&exception; proc sql noprint; select distinct quote(CND_CD), quote(CND_GRP_CD), EXCEPTION_TYPE into :cnd_cd_exc&exception separated by ',', :cnd_grp_cd_exc&exception separated by ',', :exc_type&exception from REWARDS.REGISTRATION where EXCEPTION_CD = "&exception"; quit; %put codes: &&cnd_cd_exc&exception; %put grp_cd: &&cnd_grp_cd_exc&exception; %put types: &&exc_type&exception; %end; I put in a step to delete previous values, in case you run the code more than once in the same session.   ... View more

I would do it something like this: proc summary nway data=have; class individual qtr; var var; output out=qtr_sum sum=; run; data lagged_avg; do count=0 by 1 until(last.individual); set qtr_sum; by individual; if count>0 then lagged_avg=_sum/count; output; _sum=sum(_sum,var); end; keep individual qtr var lagged_avg; run; The first PROC SUMMARY is like your first SQL step, you can also use that. I just used SUMMARY to see if there were any observations with the same individual and quarter. There was not, _FREQ_ was 1 for all obs. But that may not be the case with your real data, of course.   The datastep calculates the average of all the previous VAR values per individual. The data needs to be sorted for that; you will have to add an ORDER BY to your first SQL statement, if you use that, or a PROC SORT i you find out that the first summation is really unnecessary - PROC SUMMARY took care of sorting in my example. ... View more

One possibility is to use macro comments (start with %*) instead of normal comment lines (starting with *): proc SQL; select * from sashelp.cars where make = 'Acura' %*filter by make; order by model; ; quit; ... View more

Something like this? data want; set tmp1; last_date=intnx('month',mdy(input(substr(month,2),2.),1,year),0,'E'); select(weekday(last_date)); when(1) last_working_day=last_date-2; /* Sunday */ when(7) last_working_day=last_date-1; /* Saturday */ otherwise last_working_day=last_date; end; format last_date last_working_day date9.; run; You wrote that you want the last business date - I put in a check for the weekday, and put the result in last_working_day. But you may have to check for some national holidays as well.   ... View more