About mkeintz

mkeintz · ‎07-25-2013

Do you have to generate sql code with a "group by" expresssion? You could ignore the nested structure of the rules, and quite easily produce this code: proc sql; create table op as select label1 , label2 , label3,condition, case catx('/',label1,label2,label3,condition) when ("a/d/p/code1" ) then 'y' when ("a/d/p/code7" ) then 'y' when ("a/e/q/code2" ) then 'y' when ("a/e/q/code3" ) then 'y' when ("b/p/code4" ) then 'y' when ("b/a/r/code5" ) then 'y' when ("d/a/r/code6" ) then 'y' else 'n' end as meta_flag from new; quit;

mkeintz · ‎07-23-2013

The SET statement INTERLEAVES observations, so the resulting data set has N1 (n records in pos_tinofc) + N2 (pos_tinop) + N3 (pos_tinip) records. Now if the only variable in common in the 3 data sets is STRATA, then the MERGE statement will work (I assume as data set has no more than one record per stratum). data want; merge pos_tinofc pos_tinop pos_tinip; by strata; run; If your 3 data sets have other variables in common, then let us know. regards, Mark

mkeintz · ‎12-04-2012

I think you're putting your macro at too high a level. Use it to just generate var names in an array statement. Presumably the final code should look something like this (for the dwp vars only): data want; set have; array dwp {2005:2012} dwp05a dwp06a dwp07a dwp08a dwp09a dwp10a dwp11a dwp12a; dwp{pol_yyyy}=sum_of_dir_wp_amt; run; So the array declaration with lower bound 2005 and upperbound 2012 takes care of the "case when ..." logic. Now the only problem is to generate an array with bounds determined by global macrovars start_policy_year and end_policy_year, and to generate a list of variable names using the last two digits of the year in the middle of the var names, and a fixed prefix (dwp) and fixed suffix (a). So here's a macro: %macro expand(pre=,suf=,beg=,end=); %do I=&beg %to &end; &pre%substr(&I,3,2)&suf %end; %mend; and here's how I would use it: data want; set have; array dwp {&start_policy_year:&end_policy_year} %expand(pre=dwp,suf=a,beg=&start_policy_year,end=&end_policy_year); array exp {&start_policy_year:&end_policy_year} %expand(pre=exp,suf=a,beg=&start_policy_year,end=&end_policy_year); dwp{pol_yyyy}=sum_of_dir_wp_amt; exp{pol_yyyy}=sum_of_writ_expo_amt; run; You can easily test for out-of-bounds value of pol_yyyy, as in: if &start_policy_year <= pol_yyyy <= &end_policy_year then do; dwp{ppl_yyyy} = sum_of_dir_wp_amt; exp{pol_yyyy}=sum_of_writ_expo_amt; end; regards, Mark

mkeintz · ‎12-04-2012

I greatly appreciate the syntax PROC OPTMODEL uses in declaring array and matrics - especially for facilitating cell identification based on flexible and varied index sets. Excellent for subsetting, and visually recognising what combination of dimensions a given cell represents. And the user does not have to worry about reording one matrix to match another, as long as they are based on the same SET dimensions. BUT below (in the VAR statement at the bottom - note the syntax in red) is how, to the best of my knowledge, one has to generate an X'X sum of squares and cross products from a matrix X. That's not so bad, but what if I wanted to generate, say, beta=(X'X) -1 (X'Y) or something a bit more extensive, yet trivially expressible in matrix form? There would a lot of "sum .. in". expressions and explicit subscripting requred - easy opportunities for typing erros.. How much nicer and more readable it would be to adapt some syntax from proc IML. Something like the violet in: var beta{cols} init inv(X`*X) * X`*Y; and have it work as long as X' and Y are compatible in their declared dimensions. If somebody is aware of such syntax in PROC OPTMODEL, please point me to it. Otherwise, this is a request for adding some IML-like syntax to proc optmodel statements. ** Sample prog **; data have; do r=1 to 6; 100*uniform(015981098)); 100*uniform(015986783)); 100*uniform(015984565)); output; end; proc optmodel; set rows init {1..6}; set cols init {1..3}; num X{rows,cols} ; read data have into rows= {c in cols} <x[r,c] =col( 'c'||c)>; var XpX{c1 in cols,c2 in cols} init sum{r in 1..6} X[r,c1]*X[r,c2];

mkeintz · ‎09-24-2012

Tom has pointed out the erroneous assumptions your program embodies about the non-executable nature of the ARRAY statement, and even if the program ran to completion it wouldn't do anything. But assuming the program did do some real work, (say you were looking for the 2nd largest value in a collection), you could still use &CNT as an upper bound if you used 0 as a lower bound, as in ARRAY DTL {0:&CNT} $10. _TEMPORARY_; The array statement is still not executable, but if &CNT is zero, this statement would have avoided the implicit definition of an array with lower bound of 1 (the default) and upper bound of 0. That's what provoked the "upper bound of an array is smaller than ..." message.

mkeintz · ‎09-13-2012

Art: It may not be in the SAS docs so you would have to put it in to avoid relying on undocumented behavior. OTOH, it's almost certainly costless (in terms of computing resources) because it is probably true (as per our shared belief) that GROUP BY has activated the sort.

mkeintz · ‎09-12-2012

You have constructed HAVE so that for each distinct ACCT, there is no variation in the other fields. Given my understanding of your question, this appears to be by design. After all, you want one record per acct, so only one set of values can be displayed for the other vars.: If so, then: proc sql; select distinct * from have; quit;

mkeintz · ‎09-04-2012

The ^^ is functional, but I believe the "++" is strictly a mnemonic device, to be visually symmetric to: b +- (expression) Functionally b ++ (expression) is the same as b + (expression)

mkeintz · ‎09-04-2012

Even in this case, I would avoid a hash. Whenever I have a choice between regular arrays and hash tables, I almost always find the arrays more straightforward, and they probably are more efficient at large scales. Here's a two-step, (but one-pass solution) that uses arrays and a merge statement. It assumes only that all values of activity are integers in the range of 1 through 1000 (easily modified): data vgrps (keep=subjid _grp:) /view=vgrps; array _grp {1000}; do until (last.subjid); set test1; by subjid; do _N_=coalesce(awake,sleep) to coalesce(sleep,awake); _grp{_N_}=group; end; end; run; data want (drop=_:); array _grp {1000}; merge vgrps test2 (in=in2); by subjid; if in2 then do; group=_grp{activity}; output; end; run; Now, if you could be certain that every id appears in both test1 and test2, you could collapse this into a single step, but I wouldn't recommend it.

mkeintz · ‎09-04-2012

f you know the largest gap in A, (i.e. the largest number of B records that separate 2 A records), then you can do this (which assumes the largest gap is 4 B records). It provides a single step solution, without reversion to hash or DOW loop: data want; set a b; by x y m; z=coalescec(z,lag(z),lag2(z),lag3(z),lag4(z)); var1=coalesce(var1,lag(var1),lag2(var1),lag3(var1),lag4(var1)); run; This assumes (1) A and B are sorted by x y m, and (2) first record for a given X/Y is from A which always has a valid value for Z and VAR1.

mkeintz · ‎08-28-2012

OK, I understand now. Then how about a union of the "where trtel" where you count the subject id's with the "leg_sort eq 'placebo'" case with N specified as missing. I only tested this SQL code with your 3 observations, but it worked in that case (I prefer proc summary). I moved the respective WHERE clauses into data set name parameters so that I didn't have to figure out their influence as sql expressions. SQL mavens might have a much more elegant way to do this. proc sql; select count(distinct subjid) as n, leg_sort, 'Subjects Available for AE' as text from new (where=(trtrel eq 1)) union select . as n, leg_sort, 'Subject Available for AE' as text from new (where=(leg_sort eq 'placebo')) group by leg_sort; quit;

mkeintz · ‎08-28-2012

I did not demonstrate the proc summary - that was Mike Zdeb. My suggestion was explicitly for your PROC SQL which currently has where trtel=1 and could easily be changed to where trtel=1 or leg_sort='placebo'

mkeintz · ‎08-27-2012

Could you not then just add "or leg_sort='placebo'" to your where clause? As in: where trtel=1 or leg_sort='placebo'

mkeintz · ‎08-27-2012

Make is date=intnx('month',current_date,0,'end');

mkeintz · ‎08-27-2012

The simplest way is to; data vtemp /view=vtemp; set temp; date=intnx('month',date,0,'end'); run; proc sumary data=vtemp .....; class date; run;

Online Status	Offline
Date Last Visited	‎11-28-2025 10:28 PM