topic Re: getting the minimum observations but whilst dealing with variable in SAS Procedures

getting the minimum observations but whilst dealing with variable

andy_wk — Thu, 29 May 2014 17:39:18 GMT

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.

Re: getting the minimum observations but whilst dealing with variable

data_null__ — Thu, 29 May 2014 19:09:14 GMT

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

Re: getting the minimum observations but whilst dealing with variable

ballardw — Thu, 29 May 2014 19:28:18 GMT

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.

Re: getting the minimum observations but whilst dealing with variable

data_null__ — Thu, 29 May 2014 20:01:53 GMT

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

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

NOTE: x=_

Re: getting the minimum observations but whilst dealing with variable

andy_wk — Thu, 29 May 2014 20:21:41 GMT

Hi Ballardw,

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

Thanks,

Andy

Re: getting the minimum observations but whilst dealing with variable

andy_wk — Thu, 29 May 2014 20:42:47 GMT

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?

Re: getting the minimum observations but whilst dealing with variable

data_null__ — Thu, 29 May 2014 20:54:00 GMT

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=_:) 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 

Re: getting the minimum observations but whilst dealing with variable

andy_wk — Thu, 29 May 2014 20:58:29 GMT

Argh.

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

Is there no other way to deal with that?

Re: getting the minimum observations but whilst dealing with variable

data_null__ — Thu, 29 May 2014 22:48:39 GMT

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; 

Re: getting the minimum observations but whilst dealing with variable

andy_wk — Fri, 30 May 2014 07:03:00 GMT

Dear sir,

Thanks for your answer.

You gave me my morning smile.

And true , this is the why he is the boss

Andy

Re: getting the minimum observations but whilst dealing with variable

Ksharp — Fri, 30 May 2014 13:44:07 GMT

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

Re: getting the minimum observations but whilst dealing with variable

andy_wk — Fri, 30 May 2014 14:27:20 GMT

He is old school

But I like SQL.

Thanks Xia.

Re: getting the minimum observations but whilst dealing with variable

data_null__ — Fri, 30 May 2014 16:38:52 GMT

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