topic Re: Case to if in SAS Programming

topic Re: Case to if in SAS Programming https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123999#M25409 <HTML><HEAD></HEAD><BODY><P>Thanks you Mr.<SPAN style="font-size: 12px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;"><STRONG><A _jive_internal="true" class="jiveTT-hover-user jive-username-link" data-avatarid="1025" data-externalid="" data-presence="null" data-userid="5068" data-username="Hai.kuo" href="https://communities.sas.com/people/Hai.kuo" id="jive-506878243889954757803" style="padding: 0 3px 0 0; font-weight: inherit; font-style: inherit; font-size: 1.1em; font-family: inherit; color: #0e66ba; text-decoration: underline;">Hai.kuo.  </A></STRONG></SPAN></P><P>I executed the case which you suggested but every thing went fine. Again I was struck at one point how to apply function.</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">Proc sql:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">    create table test1 as</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">      select</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">          sum(case when trim(left(bucket_f.))= '0' then next_bal else 0 end)  as B0 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '1' then next_bal else 0 end)  as B1 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '2' then next_bal else 0 end)  as B2 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '3' then next_bal else 0 end)  as B3 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '4' then next_bal else 0 end)  as B4 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '5' then next_bal else 0 end)  as B5 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '6' then next_bal else 0 end)  as B6 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '7' then next_bal else 0 end) as B7PLUS ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">from test2;</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">quit;</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;"></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">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 "<SPAN style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">bucket_f</SPAN>".</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">Can you tell me that</P></BODY></HTML> Thu, 29 Aug 2013 06:12:18 GMT pallis 2013-08-29T06:12:18Z Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123996#M25406 <HTML><HEAD></HEAD><BODY><P>{Code}</P><P>        Proc sql:</P><P>    create table test1 as</P><P>      select  </P><P>          sum(case when trim(left(bucket_f.))= '0' then next_bal else 0 end)  as B0 ,</P><P>            sum(case when trim(left(bucket_f.))= '1' then next_bal else 0 end)  as B1 ,</P><P>            sum(case when trim(left(bucket_f.))= '2' then next_bal else 0 end)  as B2 ,</P><P>            sum(case when trim(left(bucket_f.))= '3' then next_bal else 0 end)  as B3 ,</P><P>            sum(case when trim(left(bucket_f.))= '4' then next_bal else 0 end)  as B4 ,</P><P>            sum(case when trim(left(bucket_f.))= '5' then next_bal else 0 end)  as B5 ,</P><P>            sum(case when trim(left(bucket_f.))= '6' then next_bal else 0 end)  as B6 ,</P><P>            sum(case when trim(left(bucket_f.))= '7' then next_bal else 0 end) as B7PLUS ,</P><P>from test2;</P><P>quit;</P><P>How can i write this is seperate data setp with alias name as B0.B1.. B7 plus using if condition</P><P></P><P>Regards,</P><P>venky</P></BODY></HTML> Wed, 28 Aug 2013 12:30:14 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123996#M25406 pallis 2013-08-28T12:30:14Z Re: Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123997#M25407 <HTML><HEAD></HEAD><BODY><P>I got solution;</P><P>ex :</P><P></P><P></P><P>data test1;</P><P>input a;</P><P>datalines;</P><P>1</P><P>2</P><P>3</P><P>;</P><P>run;</P><P></P><P></P><P>data test2;</P><P>set test1;</P><P>if a=1 then  desc="One" ;</P><P>else if a=2 then desc="two" ;</P><P>else desc="three";</P><P>run;</P><P></P><P>This is how I did.</P><P></P><P>regards,</P><P>venky</P></BODY></HTML> Wed, 28 Aug 2013 13:01:08 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123997#M25407 pallis 2013-08-28T13:01:08Z Re: Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123998#M25408 <HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>data test2;</P><P>set test1;</P><P>select (a);</P><P>when (1) desc="One" ;</P><P>when (2) desc="two" ;</P><P>otherwise desc="three";</P><P>end;</P><P>run;</P><P></P><P>Haikuo</P></BODY></HTML> Wed, 28 Aug 2013 13:51:35 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123998#M25408 Haikuo 2013-08-28T13:51:35Z Re: Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123999#M25409 <HTML><HEAD></HEAD><BODY><P>Thanks you Mr.<SPAN style="font-size: 12px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;"><STRONG><A _jive_internal="true" class="jiveTT-hover-user jive-username-link" data-avatarid="1025" data-externalid="" data-presence="null" data-userid="5068" data-username="Hai.kuo" href="https://communities.sas.com/people/Hai.kuo" id="jive-506878243889954757803" style="padding: 0 3px 0 0; font-weight: inherit; font-style: inherit; font-size: 1.1em; font-family: inherit; color: #0e66ba; text-decoration: underline;">Hai.kuo.  </A></STRONG></SPAN></P><P>I executed the case which you suggested but every thing went fine. Again I was struck at one point how to apply function.</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">Proc sql:</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">    create table test1 as</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">      select</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">          sum(case when trim(left(bucket_f.))= '0' then next_bal else 0 end)  as B0 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '1' then next_bal else 0 end)  as B1 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '2' then next_bal else 0 end)  as B2 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '3' then next_bal else 0 end)  as B3 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '4' then next_bal else 0 end)  as B4 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '5' then next_bal else 0 end)  as B5 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '6' then next_bal else 0 end)  as B6 ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">            sum(case when trim(left(bucket_f.))= '7' then next_bal else 0 end) as B7PLUS ,</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">from test2;</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">quit;</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;"></P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">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 "<SPAN style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">bucket_f</SPAN>".</P><P style="font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; background-color: #ffffff;">Can you tell me that</P></BODY></HTML> Thu, 29 Aug 2013 06:12:18 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/123999#M25409 pallis 2013-08-29T06:12:18Z Re: Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/124000#M25410 <HTML><HEAD></HEAD><BODY><P>My requirement was has to be done without SQL.</P><P>data a;</P><P>input a1 b1 ;</P><P>datalines;</P><P>1 4</P><P>2 5</P><P>3 3</P><P>4 2</P><P>5 9</P><P>;</P><P>run;</P><P></P><P></P><P>data b;</P><P>set a;</P><P>length desc $10.;</P><P>select (a1);</P><P>when (1) desc=max(b1);</P><P>when (2) desc=min(b1);</P><P>otherwise desc="0";</P><P>end;</P><P>run;</P><P></P><P>Error :</P><P>160  when (1) desc=max(b1);</P><P>                   ---</P><P>                   71</P><P>ERROR 71-185: The MAX function call does not have enough arguments.</P><P></P><P></P><P>161  when (2) desc=Min(b1);</P><P>                   ---</P><P>                   71</P><P>ERROR 71-185: The MIN function call does not have enough arguments.</P><P></P><P>Can you let me know how to resolve this</P></BODY></HTML> Thu, 29 Aug 2013 06:36:49 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/124000#M25410 pallis 2013-08-29T06:36:49Z Re: Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/124001#M25411 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>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 <SPAN style="font-family: 'courier new', courier;">max()</SPAN> and <SPAN style="font-family: 'courier new', courier;">min()</SPAN> functions indicates you should supply more than one value.</P><P></P><P>Documentation for the <SPAN style="font-family: 'courier new', courier;">max()</SPAN> function can be found here:</P><P></P><P><A href="http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245913.htm" title="http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245913.htm">SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition</A></P><P></P><P>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.</P><P></P><P></P><P>Further, the <SPAN style="font-family: 'courier new', courier;">strip(argument)</SPAN> function will run quicker than the <SPAN style="font-family: 'courier new', courier;">trim(left(argument))</SPAN> function combination.</P><P></P><P>Regards,</P><P>Amir.</P></BODY></HTML> Thu, 29 Aug 2013 12:25:03 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/124001#M25411 Amir 2013-08-29T12:25:03Z Re: Case to if https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/124002#M25412 <HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>It really isnt very clear what your objective is and why sql is out?</P><P></P><P>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:</P><P>data a;</P><P>input a1 b1 ;</P><P>datalines;</P><P>1 4</P><P>2 5</P><P>3 3</P><P>4 2</P><P>5 9</P><P>;</P><P>run;</P><P></P><P>proc means data=a min max noprint;</P><P>  var b1 ;</P><P>  output out=minmax (where=(_stat_ in ('MAX', "MIN")) keep=_stat_ b1);</P><P>run;</P><P></P><P>data _null_;</P><P>  set minmax;</P><P>  if _stat_ = "MAX" then call symput("max",b1);</P><P>  if _stat_ = "MIN" then call symput("min",b1);</P><P>run;</P><P></P><P>%put &min &max;</P><P></P><P>data b;</P><P>  length desc $ 10;</P><P>  set a;</P><P>  if a1 = 1 then desc = compress("&max");</P><P>  else if a1 = 2 then desc = compress("&min");</P><P>  else desc = "0";</P><P>run;</P><P></P><P>EJ</P></BODY></HTML> Thu, 29 Aug 2013 12:25:40 GMT https://communities.sas.com/t5/SAS-Programming/Case-to-if/m-p/124002#M25412 esjackso 2013-08-29T12:25:40Z