topic SAS Macro: Categorizing a Numerical variable in SAS Programming

SAS Macro: Categorizing a Numerical variable

ehsanmath — Tue, 19 Jul 2016 10:22:37 GMT

Hi,

I am looking for a Macro to catagorize a numeric variable into a catagorical variable by forming groups.

For example, I have a column called price and I want to divied it in 10 catagories.

Do someone knows such a Macro (Code) ?

thanks

Ehsan

Re: SAS Macro: Catagorizing a Numerical variable

RW9 — Tue, 19 Jul 2016 08:12:04 GMT

Why do you need a macro. This is data manipulation, should be done in a datastep which is what that is for. Macro is not for this task, it doesn't have the data types or conding structures. If you post example test data (as a datastep) and what the output should look like, I can provide code, but:

if <> then

select() when()

Could be used, or in fact formats with ranges:

proc format;

value fmt

1-10="First"

...

;

Re: SAS Macro: Catagorizing a Numerical variable

gamotte — Tue, 19 Jul 2016 08:15:38 GMT

hello,

You can use proc format

data prices;
input item $ price;
cards;
a 2
b 50
c 123
;
run;

proc format;
value price low-<10="cheap"
10-<50="ok"
50-high="expensive";
run;

proc print data=prices;
format price price.;
var item price;
run;

Re: SAS Macro: Catagorizing a Numerical variable

Ksharp — Tue, 19 Jul 2016 08:35:58 GMT

It depend on the rule how you category this price .

Check PROC RANK + groups=10 option .

proc rank data=have out=want groups=10;

var price;

ranks rank_price;

run;

Re: SAS Macro: Catagorizing a Numerical variable

ehsanmath — Tue, 19 Jul 2016 09:21:12 GMT

Actually, I am looking for an Automated (Macro) solution of this problem.

I habe more than 400 numeric columns with numeric values having different ranges (min max mean etc) .

So I need a Macro that takes as Input a Dataset and variable name and automatically make N groups(catagories) out of this column without puting manual effort as you guys have already suggested above.

So do someone knows an automated solution of this problem ?

Ehsan

Re: SAS Macro: Catagorizing a Numerical variable

gamotte — Tue, 19 Jul 2016 14:46:35 GMT

Assuming you want to categorize all columns of your dataset, otherwise you have to

refine the SQL query.

I use Ksharp's solution, proc rank, which I didnt know of. It is only slightly adapted to

automatically generate the variables and ranks names.

Here the source dataset's name is "prices"

proc sql;
SELECT NAME, cats("RANK_",NAME)
INTO :cols SEPARATED BY ' ',
     :ranks SEPARATED BY ' '
FROM dictionary.columns
WHERE LIBNAME="WORK" AND MEMNAME="PRICES" /* AND other restrictions */; /* /!\ use upper case here */
quit;

proc rank data=prices out=want groups=10;
var &cols.;
ranks &ranks.;
run;

Re: SAS Macro: Catagorizing a Numerical variable

ehsanmath — Wed, 20 Jul 2016 08:07:47 GMT

Actually I was looking for ready-made solution. Its sounds like that there is now such known solution available. So I have to put the Ideas given by you guys in a code.