Help using Base SAS procedures

getting the minimum observations but whilst dealing with variable

Reply
Frequent Contributor
Posts: 75

getting the minimum observations but whilst dealing with variable

I have the following issue. I'm trying to have the following result:

If &&&&var&&pays&l.._&i.._t&j is negative then varnum_&i._&j=log(min(&&&&var&&pays&l.._&i.._t&j)) e.g. choose the minimum observation for that variable.

However, as you can see, I'm dealing with variables rather than only observations.

I'm clueless on that one.

All the code is below.

data afr_test2 ; set afr_test1 ; %do j=0 %to 5 ;%do i=1 %to &&&&nvar&&pays&l ; format varnum_&i._&j 10.3 ; /*2eme modele: */ /* varnum_&i._&j=&&&&var&&pays&l.._&i.._t&j;*/ if &&&&var&&pays&l.._&i.._t&j < . then do ; varnum_&i._&j=log(min(&&&&var&&pays&l.._&i.._t&j)); else varnum_&i._&j=log(&&&&var&&pays&l.._&i.._t&j); end; if varnum_&i._&j eq . then varnum_&i._&j=0; %end ; %end; run ; 

I was thinking of using a proc sql and create a table to get all the minimum observation for that variables.

Something like that

proc sql: create table toto as select min(val) from table_name;

But how would I get the variable name, side by side with the minimum from that specific variable?

Thanks for any insight you can provide.

Respected Advisor
Posts: 3,799

Re: getting the minimum observations but whilst dealing with variable

Can you show your data and the result you are after.  I'm sorry but I'm to old for &&&&:smileyshocked:

Super User
Posts: 11,343

Re: getting the minimum observations but whilst dealing with variable

f &&&&var&&pays&l.._&i.._t&j < .  won't ever be true . (missing is smallest value) you want

f &&&&var&&pays&l.._&i.._t&j < 0 to indicate negative.

Respected Advisor
Posts: 3,799

Re: getting the minimum observations but whilst dealing with variable

Actually missing underscore ._ is smallest but we know what you mean.

data _null_;
  
x = ._ min .;
  
put 'NOTE: ' x=;
   run;

NOTE:
x=_
Frequent Contributor
Posts: 75

Re: getting the minimum observations but whilst dealing with variable

Posted in reply to data_null__

Hi Data Null,

The ._ means the smallest record?

I'm trying to do the following:

For each records that is negative, give me the smallest value plus the actual value of the observation for that variable

Example:

var1 var2 var3
.     .   .

1   3   5

-2 -2  -1

3   5   1

For var1, I would have -2 plus 1. For var2, I would have -2 plus 3 etc...

Do you want me to clean the code?

Respected Advisor
Posts: 3,799

Re: getting the minimum observations but whilst dealing with variable

Is it this?

data have;
   input var1-var3;
   cards;
.     .   .
1   3   5
-2 -2  -1
3   5   1
;;;;
   run;
proc summary data=have;
   output out=min(drop=_Smiley Happy min(var1-var3)=min1-min3;
   run;
data want;
   set have;
   if _n_ eq 1 then set min;
   new1=sum(var1,min1);
   new2=sum(var2,min2);
   new3=sum(var3,min3);
  
run;
proc print;
  
run;

Obs    var1    var2    var3    min1    min2    min3    new1    new2    new3

1       .       .       .      -2      -2      -1      -2      -2      -1
2       1       3       5      -2      -2      -1      -1       1       4
3      -2      -2      -1      -2      -2      -1      -4      -4      -2
4       3       5       1      -2      -2      -1       1       3       0
Frequent Contributor
Posts: 75

Re: getting the minimum observations but whilst dealing with variable

Posted in reply to data_null__

Argh.

So my boss was right, you actually need a proc summary ...

Is there no other way to deal with that?

Respected Advisor
Posts: 3,799

Re: getting the minimum observations but whilst dealing with variable

Maybe that's why he's the boss.

You can do it the hard way.

data have;
   input var1-var3;
   cards;
.     .   .
1   3   5
-2 -2  -1
3   5   1
;;;;
   run;
*the hard way;
data want;
   do while(not eof);
      set have end=eof;
      array v
  • var:;
  •       array m[3] _temporary_;
         
    do i = 1 to dim(v);
             m=min(m,v);
            
    end;
         
    end;
       eof=
    0;
      
    do while(not eof);
          set have end=eof;
          do i = 1 to dim(v);
             v=sum(v,m);
            
    end;
         
    output;
         
    end;
       stop;
      
    drop i;
       run;
    proc print;
      
    run;

    Frequent Contributor
    Posts: 75

    Re: getting the minimum observations but whilst dealing with variable

    Posted in reply to data_null__

    Dear sir,

    Thanks for your answer.

    You gave me my morning smile.

    And true , this is the why he is the boss Smiley Happy

    Andy

    Super User
    Posts: 10,044

    Re: getting the minimum observations but whilst dealing with variable

    If your boss know or like SQL ?

    data have;
       input var1-var3;
       cards; 
    .     .   .
    1   3   5
    -2 -2  -1
    3   5   1
    ;;;;
       run; 
    
    proc sql;
    create table want as
     select *,var1+(select min(var1) from have) as new1,
              var2+(select min(var2) from have) as new2,
                var3+(select min(var3) from have) as new3
      from have;
    quit;
    

    Xia Keshan

    Frequent Contributor
    Posts: 75

    Re: getting the minimum observations but whilst dealing with variable

    He is old school Smiley Happy

    But I like SQL.

    Thanks Xia.

    Respected Advisor
    Posts: 3,799

    Re: getting the minimum observations but whilst dealing with variable

    When is SQL not old school?  Over 20 years as PROC SQL and SQL has been around much longer than that.

    Frequent Contributor
    Posts: 75

    Re: getting the minimum observations but whilst dealing with variable

    Hi Ballardw,

    I though that . was meaning 0. This is why I've put the dot.

    Thanks,

    Andy

    Ask a Question
    Discussion stats
    • 12 replies
    • 290 views
    • 4 likes
    • 4 in conversation