DICTIONARY.TABLES is good for that: proc sql; select memname,nlobs from dictionary.tables where libname='MAIN'; quit; I used NLOBS rather than NOBS, because NOBS includes deleted observations. You can use a create table clause to put the stuff in a dataset, or you can put the table names that you want in a macro variable, e.g.: proc sql noprint; select memname into :notempty separated by ' ' from dictionary.tables where libname='MAIN' and NLOBS>0; quit; ... View more

The solution suggested by @Reeza has a slight snag: times 00:00 to 00:29 show up as "0", times 00:30 to 01:29 show up as "1", etc, up to 23:30 to 23:59, which show up as "24" with the HOUR format. So you will get 25 hour categories, two of which ("0" and "24") are actually just half hours. Also, the merge by time_value makes it necessary to sort the data first.   I would rather calculate the (truncated) hour values with the HOUR() function, and instead of merging just output all the potential missing records in the same step: data hourly; set have; by date; hour=hour(time); output; if last.date; /* set the variables to be summarized missing */ x=.; y=.; do hour=0 to 23; output; end; drop time; run; And then, summarize: proc summary data=hourly nway; class date hour; var x y; output out=means(drop=_:) mean=; run; You then have a table with all hours from 0 to 23, and the analysis variables misising when there are no data for that hour.   I do not know the names of your numeric variables, so I just called them X and Y. And you may want to include your character variables in the BY and CLASS statements as well. ... View more

That can also be done reading the data just once: data want; set s1; by id; _sum+dose; if last.id then do; sum=_sum; _sum=0; end; drop _sum; run; ... View more

One possibility is to extract the run dates from your calendar, like this: Data rundates(index=(date)); set calendar; where bus_ind=1; if month(date) ne month(lag(date)); keep date; run; Then, in the top of your batch program put a datastep like this; data _null_; date=today(); set rundates key=date; if _iorc_ then do; put 'Today is not first business day of month, aborting'; _error_=0; abort return 0; end; run; Of course, you will want to store you rundates table in a permanent library. ... View more

The easiest way to do this is probably storing the returns in an array, something like: Data want; set have; by Stock_code: array returns(0:90) 8 _temporary_; /* 3 months is about 91 days */ if first.Stock_code then call missing(of returns(*)); returns(mod(_N_,91))=daily_return; if n(returns)>1 then st_dev=std(of returns(*)); run; Note the use of a zero-based array, it makes the calculation of the array index a simple modula of _N_. ... View more

If I understand you correctly, you want to sum a number of variables, based on whether the variable name (which indicates a month) falls within a certain period.   If (and only if) your variables occur in the table in the correct order, with no other variables in between, you can try something like this: DATA test; SET mem(OBS=1); DT_CHECK = intnx('month', EVENT_DT, -10); DT_CHECK_STR = cats('Y', put(DT_CHECK, YEAR2.), '_', 'M', month(DT_CHECK)); HYST_DT_STR = cats('Y', put(EVENT_DT, YEAR2.), '_', 'M', month(EVENT_DT)); ARRAY vars[*] Y80_M1--Y12_M3; DO i=1 TO dim(vars) until(varname(vars(i))=DT_CHECK_STR); end; do i=i to dim(vars) until(varname(vars(i))=HYST_DT_STR); sum_n+vars{i}; end; FORMAT DT_CHECK mmddyy10.; drop i; RUN; The first DO loop finds the variable that matches DT_CHECK_STR, the second sums all the variables up to (and including) the one matching HYST_DT_STR.   However, it should also be possible to calculate the starting index: data test; set mem; array vars(*) Y80_M1--Y12_M3; DT_CHECK=intnx('month',EVENT_DT,-10); start=intck('month','01jan1980'd,DT_CHECK)+1; if start<1 then do; error 'Start date out of range'; delete; end; else if start>dim(vars)-10 then do; error 'End date out of range'; delete; end; do _N_=start to start+10; sum_n+vars(_N_); end; format DT_CHECK mmddyy10.; run; This method is faster, and it is also safer, due to the range checks. Both methods assume that there are columns for all the months in all the years. ... View more

Another possibility is to use a macro: %macro stack(inds,nvars); %local dsid i varnum; %let dsid=%sysfunc(open(&inds)); set &inds; %do i=1 %to &nvars; %let varnum=%sysfunc(varnum(&dsid,var&i)); %if %sysfunc(vartype(&dsid,&varnum))=N %then %do; if not missing(var&i) then do; newcol=var&i; output; end; %end; %else %do; if not missing(var&i) then do; newcol=input(var&i,best32.); output; end; %end; %end; %let dsid=%sysfunc(close(&dsid)); drop var1-var&nvars; %mend; data want; %stack(have,100); run; The parameters to the macro are the name of the input dataset, and the number of variables. ... View more

Something like this? %LET RUNDATE = "01APR2018"D; DATA _NULL_; if month(&rundate) ge 4 then add=3-month(&rundate); else add=-5-month(&rundate); do i=1 to 8; call symput(cats("MONTH",i),put(intnx('MONTH',&RUNDATE,i+add),YYMMN6.)); end; run; %PUT MONTH1 = &MONTH1; %PUT MONTH2 = &MONTH2; %PUT MONTH3 = &MONTH3; %PUT MONTH4 = &MONTH4; %PUT MONTH5 = &MONTH5; %PUT MONTH6 = &MONTH6; %PUT MONTH7 = &MONTH7; %PUT MONTH8 = &MONTH8; ... View more

I think it is mostly getting rid of the CAST calls, as they are not in SAS SQL. The CASTs to FLOAT are unnecessary, as SAS numbers are floats anyway. The CAST to a string is not necessary, just put a format on the number to display it as a percent: SELECT Employee_Name, ProjectCode, QCErrorLevelName, SUM(Perc) format=percent7.2 as Error_Rate FROM (select COUNT(DISTINCT SafetyCaseID)/ (select COUNT(Distinct SafetyCaseID) from dbo.VWCDSCPQualityMetricsRpt P1 where P1.ProjectCode = P.ProjectCode and P1.Employee_Name = P.Employee_Name GROUP BY ProjectCode,Employee_Name) as Perc, ProjectCode, Employee_Name, CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End as QCErrorLevelName from dbo.VWCDSCPQualityMetricsRpt P where QCErrorLevelName LIKE 'L%' GROUP BY ProjectCode,Employee_Name,(CASE WHEN QCErrorLevelName LIKE 'L%' Then QCErrorLevelName ELSE 'No Error' End) ) A GROUP BY ProjectCode, QCErrorLevelName,Employee_Name; ... View more