turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Case to if

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-28-2013 08:30 AM

{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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-28-2013 09:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-28-2013 09:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-29-2013 02:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-29-2013 02:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-29-2013 08:25 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-29-2013 08:25 AM

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