title: Extracting sub-data from a SAS dataset & applying to a different dataset
body: <p>I have written a macro to use <code>proc univariate</code> to calculate custom quantiles for variables in a dataset (say <code>dsn1</code>) <code>%cust_quants(dsn= , varlist= , quant_list= )</code>. The output is a summary dataset (say <code>dsn2</code>)that looks something like the following:</p>
  
  <pre><code>q_1      q_2.5      q_50      q_80      q_97.5      q_99      var_name
  1        2.5        50        80        97.5        99        ex_var_1_100
  -2       10         25        150       500         20000     ex_var_pos_skew
  -20000   -500       -150      0         10          50        ex_var_neg_skew
  </code></pre>
  
  <p>What I would like to do is to use the summary dataset to cap/floor extreme values in the original dataset. My idea is to extract the column of interest (say <code>q_99</code>) and put it into a vector of macro-variables (say <code>q_99_1, q_99_2, ..., q_99_n</code>). I can then do something like the following:</p>
  
  <pre><code>/* create summary of dsn1 as above example */
  %cust_quants(dsn= dsn1, varlist= ex_var_1_100 ex_var_pos_skew ex_var_neg_skew, 
               quant_list= 1 2.5 50 80 97.5 99);
  
  /* cap dsn1 var's at 99th percentile */
  data dsn1_cap;
    set dsn1;
  
    if ex_var_1_100 &gt; &amp;q_99_1 then ex_var_1_100 = &amp;q_99_1;
    if ex_var_pos_skew &gt; &amp;q_99_2 then ex_var_pos_skew = &amp;q_99_2;
    /* don't cap neg skew */
  run;
  </code></pre>
  
  <p>In <code>R</code>, it is very easy to do this. One can extract sub-data from a data-frame using matrix like indexing and assign this sub-data to an object. This second object can then be referenced later. <code>R</code> example--extracting <code>b</code> from data-frame <code>a</code>:</p>
  
  <pre><code>&gt; a &lt;- as.data.frame(cbind(c(1,2,3), c(4,5,6)))
  &gt; print(a)
    V1 V2
  1  1  4
  2  2  5
  3  3  6
  &gt; a[, 2]
  [1] 4 5 6
  &gt; b &lt;- a[, 2]
  &gt; b[1]
  [1] 4
  </code></pre>
  
  <p><strong>Is it possible to do the same thing in SAS?</strong> I want to be able to assign a column(s) of sub-data to a macro variable / array, such that I can then use the macro / array within a 2nd data step. One thought is <code>proc sql into:</code>:</p>
  
  <pre><code>proc sql noprint;
    select v2 into :v2_macro separated by " "
    from a;
  run;
  </code></pre>
  
  <p>However, this creates a single string variable when what I really want is a vector of variables (or array--no vectors in SAS). Another thought is to add <code>%scan</code> (assuming this is inside a macro):</p>
  
  <pre><code>proc sql noprint;
    select v2 into :v2_macro separated by " "
    from a;
  run;
  
  %let i = 1;
  %do %until(%scan(&amp;v2_macro, &amp;i) = "");
    %let var_&amp;i = %scan(&amp;v2_macro, &amp;i);
    %let &amp;i = %eval(&amp;i + 1);
  %end;
  </code></pre>
  
  <p>This seems inefficient and takes a lot of code. It also requires the programmer to remember which <code>var_&amp;i</code> corresponds to each future purpose. Is there a simpler / cleaner way to do this?</p>
  
  <p>**Please let me know in the comments if this is enough background / example. I'm happy to give a more complete description of why I'm doing what I'm attempting if needed.</p>