In your example, there is no place to name the fruits.  Is that intentional? ... View more

I take it the treatmenstartdate in your output is the earliest value of treatmendate for a given id/test.  In your sample, it doesn't matter since you treatmendate is constant, but this program assume it can change, even within a given id/test.   This program needs your dataset to be sorted by any of:  (id/testdate  id/test/testdate testdate/id).  It's a single pass through the data, and it uses two hash objects (think lookup tables): one with the last testdate for each id/test, and one with the earliest treatmendate for each id/test.   data want; set have (drop=treatmendate) have (obs=0 keep=testdate treatmendate rename=(testdate=last_tdate treatmendate=treatmenstartdate)); if _n_=1 then do; /* Get lookup table for last testdate, by id/test */ declare hash last_td (dataset:'have (keep=id test testdate rename=(testdate=last_tdate))' ,duplicate:'Replace'); last_td.definekey('id','test'); last_td.definedata('last_tdate'); last_td.definedone(); /* Get lookup table for first treatmendate, b id/test */ declare hash strtdate (dataset:'have (keep=id test treatmendate rename=(treatmendate=treatmenstartdate))'); strtdate.definekey('id','test'); strtdate.definedata('treatmenstartdate'); strtdate.definedone(); end; last_td.find(); /* retrieve last_tdate*/ strtdate.find(); /* retrieve treatmenstartdate*/ if testdate<=treatmenstartdate and testdate<last_tdate then flag='Y'; drop last_tdate; run; ... View more

@knveraraju91   I don't think this will do what you intend.  Testdate (all in 2015) is never >= treatmendate (all in 2016) ... View more

You essentially want PROC TABULATE to rank the mean (or sum) of the Q1 responses.    So what exactly do you want to display?   Just the 5 highest and 5 lowest Q1's?  Or all 25, with the 5 min and 5 max flagged somehow?             ... View more

  I'd recommend the following sequence:    Make sure your dataset is sorted by ID/YEAR Make a data set VIEW containing all the needed squares and cross-products - call it  SQ_CP_BY_IDYEAR Run a proc expand to generate 10-year rolling SSCP values for each firm Run proc summary to pool data over all firms Convert the rolling sscp into a TYPE=SSCP dataset, which PROC REG can process   The code here applies that process to sashelp.stocks (10 days instead of 10 years)   For a 10-year window this process of making 10-year rolling SSCP may not be particlularly faster than making 10-year rolling oriiginal data windows, but for daily and monthly data, where each window commonly has more records, it becomes notably superior.   proc sort data=sashelp.stocks out=stocks (rename=(close=y open=ylag1 high=x1 low=x2 volume=x3 adjclose=x4)); by stock date; run; /* Make a data set view of squares and cross-products */ data sqcp /view=sqcp; set stocks; array vars {7} intercept y ylag1 x1-x4; retain intercept 1; array sqcp {7,7}; do r=1 to 7; sqcp{r,r}=vars{r}**2; if r<7 then do c=r+1 to 7; sqcp{r,c}=vars{r}*vars{c}; sqcp{c,r}=sqcp{r,c}; end; end; drop r c; run; /* Make rolling sscp (SUM of squares & cross-prods) data for 10-day windows */ proc expand data=sqcp out=rsscp_data2 (where=(intercept=10)) method=none; by stock; id date; convert _numeric_ / transform=(movsum 10); run; /* Pool sscp data over all stocks */ proc summary data=rsscp_data nway; class date; var _numeric_; output out=pooled_rsscp_data (drop=_TYPE_ _FREQ_) sum=; run; /* Reformat the data as a TYPE=SSCP data set, ready for PROC REG */ data pooled_rolling_sscp (type=SSCP drop=sqcp: n v); length _TYPE_ $8 _NAME_ $32; set pooled_rsscp_data; array vars {7} intercept y ylag1 x1-x4; array sqcp {7,7}; array vnames {7} $32 _temporary_ ; if _n_=1 then do v=1 to dim(vars); vnames{v}=vname(vars{v}); end; _TYPE_='SSCP'; do n=1 to 7; _NAME_=vnames{n}; do v=1 to 7; vars{v}=sqcp{n,v}; end; output; end; _type_='N'; _NAME_=' '; do v=1 to 7; vars{v}=sqcp{1,1}; end; output; run; proc reg data=pooled_rolling_sscp (obs=8); var y ylag1 x1-x4; model y=ylag1 x1-x4; run; quit; ... View more

"On average there are 3 observations in the same group. But that is exactly what I need it to be to get my result."   And you want 1 regression per by group?   Models won't even be estimated if the by group size does not have 2 more obs than there are independent variiables.  What  possible use would it be? ... View more

You appear to be looking for character values which cannot be converted to numerics. You do this test over all vars in each row of the transposed data, setting NFLAG=1 if a convertable value occurs (else nflag=0).  But the problem (well one of the problems) is that you do this in sequence for ALL the variables, resetting NFLAG each time.  So NFLAG really only reflects the status of the last variable in each row,  (i.e. the last row of the original untransposed data set).   And then you transpose back, which by default transposes only the numeric vars (LEN, LEN2 and NFLAG), yielding pretty useless information.   So I guess what you really want is to check each column of the original data set, to see if there are any non-convertable values.  Consider this structure with improved improvised dataset HAVE.  It will (1) report to the log any vars with any non-convertable values, and (2) generate dataset BADCOUNTS with one row containing number of non-convertable values for each of the original variables:   filename tmp temp; /* Type is TEMP so it will automatically be deleted */ data _null_; set have end=eod; array chr {*} _character_; array nbad {100} _temporary_ (100*0); do I=1 to dim(chr); nbad{I}=nbad{I} + NOT(compress(chr{I},'.','d')=' '); end; if eod then do; file log; put 'These vars have at least one instance that cant be converted to numeric:'; do I=1 to dim(chr); chr{I}=put(nbad{I},3.); if nbad{I}>0 then put chr{I}=; end; file tmp; /* Write the next PUT to this destination */ put (chr{*}) (= ';') ';'; end; run; options source2; /* to print out code retrieved via the %include below */ data badcounts; %include tmp; run;       ... View more

You have not yet responded with an actual example of the result you wish to generate, so we are all trying to read your mind.  Here is my guess.  It uses the PROC MEANS that you have chosen.   Notes You will only get the maximum WITHIN class.  So use only year as a class variable, to get max within year. You want to use BUS as the id variable for the maximum passengers - so examine the MAXID clause in the output statement. Because the OUTPUT statement is being used to give names to statistics (i.e. maxwap) and id variables (maxwapbus), it doesn't generate one obs per stat per class.  It generates one obs per class, with several variables (one per stat). if there are two bus lines with maximum passengers for a given year, you will only get the name of the first one encountered in data set wap.   proc means data=wap nway noprint; class year; var wap; output out=want maxid(wap(bus))=maxwapbus max=maxwap; run; proc print data=want; var year maxwapbus maxwap; run; ... View more