{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
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
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
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
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: