For a start, I suggest: option nofmterr; That way we don't get 2,000 pages of format errors, when it could have been shown in one page!   Can you give us access to the dataset? It's hard to work on it if we can only guess. ... View more

Without having access to your data, it's not simply a case of using: proc tabulate data=dsn3 missing; is it? ... View more

It was a pleasure. by-group processing is one of the most powerful components of a data step. Other languages handle the same concept differently (or not at all!), but SAS's method is so elegant. ... View more

Assuming you just want one row per group (2!), is this it?   data want; set have; by id; array products[*] yproduct xproduct zproduct; retain yproduct xproduct zproduct; if first.id then do i = 1 to dim(products); /* Initialise products at start of id */ products[i] = 1; end; yproduct = yproduct * y; xproduct = xproduct * x; zproduct = zproduct * z; if last.id; /* Only output the last row for each id */ keep id yproduct xproduct zproduct; run; ... View more

Oh I see what you mean. OK - I'll revise that. The implicit group by of a function like max (where its the function activing over the whole query) acts after the primary query (the from and where).   Your first query is using a SAS function min; your second query is using an SQL min. They function differently. ... View more

Yup. max(weight) will always return missing (first principles). The min function picks that up after the query, and then returns the mininum value of 999 and missing (999, by definition). It's almost as if it's turning the rest into a subquery and acting on it.   It's something I quite often take advantage of when I don't want a macro variable to have a missing value:   proc sql noprint; select coalesce(max(weight), 0) into :test_query from sashelp.class where age = 2; quit; %put &=test_query; PS I like the debugging %put &=varname syntax. I never knew! ... View more

Oh - that's a little harsh!   OK here goes: it's not defining variables in itself. This is standard SAS datastep operating procedure: in any statement, like retain, length or format isn't actively defining a variable. The SAS parser goes off, creates the variable where it doesn't already exist and allows the (in this case) non-executable statement to do its thing. array doesn't do anything differently to any other statement.   The only time, as far as I'm concerned, that array defines variables is with the _temporary_ keyword, and even then it's still just putting a template over a piece of (possibly discontiguous) storage. ... View more

No, it doesn't work like that. SAS's definition of arrays is unlike most (all?) other languages. All it does is set a template over a collection of variables: there is no space allocated as such by the array statement (I will defend this against all comers!).   So when you define an array, you have to set the bounds of the array dimensions. When you use the _numeric_ or _char_ special variable names, you're assigning the array in only one dimension. WIthin the brackets you can set either the numeric bounds or, as you've done in your first example "*", to let the SAS parser pick up the bounds from metadata.   So your second example won't work for two reasons: the array bounds must be numeric constants (not variables) or *. Even having variables i and j in work.dataset (and having variables called that in a dataset is never a good idea!) won't work, because they're not constants. _numeric_ is inherently one-dimensional. It is possible to treat it as multi-dimensional, but doing that would be fraught with difficulty; I would never recommend it because you have very little control over the order in which your variables are referenced.   ... View more

The only tricky thing in your data is that it is inherently unsorted. But I believe by-group processing is your answer. Here's how I did it: data source; infile cards; input a b; cards; 1 5 1 5 1 5 1 5 1 5 2 7 2 7 2 7 2 7 2 6 2 6 2 6 ; run; data target; set source; by a b notsorted; retain sequence; select; when(first.a) sequence = 1; when(first.b) sequence + 1; otherwise; end; run;   Note the notsorted keyword on the by statement - because in a's second group 7 is higher than 6, the by processing would complain; notsorted suppresses that. ... View more