BookmarkSubscribeRSS Feed
andy_wk
Calcite | Level 5

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.

12 REPLIES 12
data_null__
Jade | Level 19

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

ballardw
Super User

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.

data_null__
Jade | Level 19

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

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

NOTE:
x=_
andy_wk
Calcite | Level 5

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?

data_null__
Jade | Level 19

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
andy_wk
Calcite | Level 5

Argh.

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

Is there no other way to deal with that?

data_null__
Jade | Level 19

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;

    andy_wk
    Calcite | Level 5

    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

    Ksharp
    Super User

    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

    andy_wk
    Calcite | Level 5

    He is old school Smiley Happy

    But I like SQL.

    Thanks Xia.

    data_null__
    Jade | Level 19

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

    andy_wk
    Calcite | Level 5

    Hi Ballardw,

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

    Thanks,

    Andy

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 12 replies
    • 893 views
    • 4 likes
    • 4 in conversation