BookmarkSubscribeRSS Feed
pallis
Fluorite | Level 6

{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

6 REPLIES 6
pallis
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

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
Fluorite | Level 6

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
PROC Star

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
Quartz | Level 8

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

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!

How to Concatenate Values

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.

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
  • 6 replies
  • 1891 views
  • 7 likes
  • 4 in conversation