topic Re: PROC SQL in SAS Programming

PROC SQL

Vaibhav_Kalra — Tue, 21 Aug 2018 08:25:44 GMT

I have got a data with 20 columns and i want categorize the data using Proc SQL as per below

If Amount spent is less than 100 than "A"

If Amount spent is between 100 and 200 than "B"

If Amount spent is greater than 200 than "C"

Thanks

Re: PROC SQL

Oligolas — Tue, 21 Aug 2018 08:38:14 GMT

Hi,

use the case Expression as explained here

Re: PROC SQL

Kurt_Bremser — Tue, 21 Aug 2018 08:38:31 GMT

proc sql;
create table want as
select
  a.*,
  case
    when amount < 100 then 'A'
    when 100 <= amount <= 200 then 'B'
    when amount > 200 then 'C'
  end as category
from have a;
quit;

Re: PROC SQL

RW9 — Tue, 21 Aug 2018 09:09:17 GMT

Please post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

And what you want to see out at the end, otherwise we are guessing on both. You say you have 20 columns, but never mention what these are, how they are used or any relevance to the code. You state using SQL, but do not give any reasons for this - SQL is built to run on normalised data - i.e. few columns, lots of observations - and as such isn't a good tool for lots of columns. Datastep and an array would be easier - obviously still guessing as no information provided. Its likely that you could just format the value with a custom format and avoid all conditional programming at all.