Case to if

[pallis]

{Code}

        Proc sql:

    create table test1 as

      select 

          sum(case when trim(left(bucket_f.))= '0' then next_bal else 0 end)  as B0 ,

            sum(case when trim(left(bucket_f.))= '1' then next_bal else 0 end)  as B1 ,

            sum(case when trim(left(bucket_f.))= '2' then next_bal else 0 end)  as B2 ,

            sum(case when trim(left(bucket_f.))= '3' then next_bal else 0 end)  as B3 ,

            sum(case when trim(left(bucket_f.))= '4' then next_bal else 0 end)  as B4 ,

            sum(case when trim(left(bucket_f.))= '5' then next_bal else 0 end)  as B5 ,

            sum(case when trim(left(bucket_f.))= '6' then next_bal else 0 end)  as B6 ,

            sum(case when trim(left(bucket_f.))= '7' then next_bal else 0 end) as B7PLUS ,

from test2;

quit;

How can i write this is seperate data setp with alias name as B0.B1.. B7 plus using if condition

Regards,

venky

[pallis]

I got solution;

ex :

data test1;

input a;

datalines;

1

2

3

;

run;

data test2;

set test1;

if a=1 then  desc="One" ;

else if a=2 then desc="two" ;

else desc="three";

run;

This is how I did.

regards,

venky

[Haikuo]

Following approach is another equivalent way to "if-else" in Data Step, it may bear some benefit in term of clearer structure and less typing when you have many conditions. It is also worth noting that in the case that you are applying the same conditions through out many data steps, using format may become more plausible.

data test2;

set test1;

select (a);

when (1) desc="One" ;

when (2) desc="two" ;

otherwise desc="three";

end;

run;

Haikuo

[pallis]

Thanks you Mr.Hai.kuo. 

I executed the case which you suggested but every thing went fine. Again I was struck at one point how to apply function.

Proc sql:

    create table test1 as

      select

          sum(case when trim(left(bucket_f.))= '0' then next_bal else 0 end)  as B0 ,

            sum(case when trim(left(bucket_f.))= '1' then next_bal else 0 end)  as B1 ,

            sum(case when trim(left(bucket_f.))= '2' then next_bal else 0 end)  as B2 ,

            sum(case when trim(left(bucket_f.))= '3' then next_bal else 0 end)  as B3 ,

            sum(case when trim(left(bucket_f.))= '4' then next_bal else 0 end)  as B4 ,

            sum(case when trim(left(bucket_f.))= '5' then next_bal else 0 end)  as B5 ,

            sum(case when trim(left(bucket_f.))= '6' then next_bal else 0 end)  as B6 ,

            sum(case when trim(left(bucket_f.))= '7' then next_bal else 0 end) as B7PLUS ,

from test2;

quit;

In this case if I go with the method whch you mentioned it works fine but how to add the functions SUM,TRIM,LEFT for column "bucket_f".

Can you tell me that

[pallis]

My requirement was has to be done without SQL.

data a;

input a1 b1 ;

datalines;

1 4

2 5

3 3

4 2

5 9

;

run;

data b;

set a;

length desc $10.;

select (a1);

when (1) desc=max(b1);

when (2) desc=min(b1);

otherwise desc="0";

end;

run;

Error :

160  when (1) desc=max(b1);

                   ---

                   71

ERROR 71-185: The MAX function call does not have enough arguments.

161  when (2) desc=Min(b1);

                   ---

                   71

ERROR 71-185: The MIN function call does not have enough arguments.

Can you let me know how to resolve this

[Amir]

Hi,

I'm not sure I fully understand how your data step code matches your SQL code, but the error message you are getting with the max() and min() functions indicates you should supply more than one value.

Documentation for the max() function can be found here:

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

In a data step, the function does *not* apply itself to the whole column of data, it just operates on the values you supply it for that one observation.

Further, the strip(argument) function will run quicker than the trim(left(argument)) function combination.

Regards,

Amir.

[esjackso]

The max function in the datastep is not accross observations it is actually looking for max across variables so passing one variable does not make sense. Plus you are combining numerical results and character results ("0") in the same variable.

It really isnt very clear what your objective is and why sql is out?

If you just want to add the desc variable you could create the variable and merge it in. Or use macro variables to assign the max and min such as below:

data a;

input a1 b1 ;

datalines;

1 4

2 5

3 3

4 2

5 9

;

run;

proc means data=a min max noprint;

  var b1 ;

  output out=minmax (where=(_stat_ in ('MAX', "MIN")) keep=_stat_ b1);

run;

data _null_;

  set minmax;

  if _stat_ = "MAX" then call symput("max",b1);

  if _stat_ = "MIN" then call symput("min",b1);

run;

%put &min &max;

data b;

  length desc $ 10;

  set a;

  if a1 = 1 then desc = compress("&max");

  else if a1 = 2 then desc = compress("&min");

  else desc = "0";

run;

EJ