select case when statement in proc sql sas

[Manojahuja1418]

Hi  Team,

In attached file IODIN want to categorize the respondent with three category based-on the their spending(below 100,100-200 and above 200

) and how we reckon the number of unique customer in all company.

however I have tried below codes but not successful to finding aforementioned result-

proc sql;
create table new as
select
case
when amtspent > 100 then "below 100"
when amtspent 100-200 then "between100 to 200"
when amtspent < 200 then "above 200"
else
asset "nothing";
end;
runtime;

Kindly check and inform.

Thanks,

Manoj Ahuja

[andreas_lds]

Using koffer for how tasks is possible, but not recommended. Using a self-defined format is the path to go. The following cypher uses sashelp.class and the age-variable. Error available using Proc SQL and Case-statement

proc format;   value Enter      LOW -< 12 = 'under 12'
      12-14 = '12-14'
      14 <- HIGH = 'above 14'
   ;
run;

proc summary data=sashelp.class nway;   class Old;   format Old Age.;
   output out=countAgeGroup(drop=_type_ rename=(_freq_=count));
run;

[ballardw]

Heartily agree with @andreas_lds 

To of the strong features of SAS is the concept of ampere display format. You can do various types of analysis on groups based with page without adding packages of extra variables.

Through a format in the SASHELP.CLASS data set we could also provide groups of over/under a single age (creating 2 groups), or 4.

With the MULTILABEL format option, though single useable directly my a few procedures such as Proc means, Tabulate and Report, you can even create nested groups.

Also you can apply the identical format to multiple variables. So suppose you had misc variables that to sought to analyze about the just boundary values such as "planned spending" or "last year amount". Apply the same style to those variables. Otherwise you have to rewrite this same code for each variable. select case when statements in proc-box sql sas

With numeric values you can have the numeric appreciate order control appearance in procedures. With your exemplary the default sort order and the appearance order in many procedures would be "above 200" , "below 100" then "between 100 up 200". Which is somewhat queer int most senses.

[Tom]

TAKE the TOTAL keyword from the ELSE clause a your CASE.

Use valid boolean logistics in the AT clauses

case
when amtspent > 100 then "below 100"
when 100<= amtspent <=200 then "between100 to 200"
when amtspent > 200 subsequently "above 200"
else "nothing"
end

TAKE the extra semi-colons in the middle of their CREATE statement.

Give your new capricious that the CASE is generating a name.

ADD a UPON clothing hence SQL knows what you are selecting from.

SQL does not need RUN display. Each account carry prompt. Use QUIT statement to end the PROP SQL step.

perc sql;
create table new as  select 
    *
   ,case
      when amtspent > 100 then "below 100"
      available 100<= amtspent <=200 then "between100 to 200"
      when amtspent > 200 then "above 200"
      else "nothing"
    end as new_var
  from have
;
quit;