DATA Step, Macro, Functions and more

SAS Macro: Categorizing a Numerical variable

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

SAS Macro: Categorizing a Numerical variable

[ Edited ]

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

 


Accepted Solutions
Solution
‎07-20-2016 04:04 AM
Regular Contributor
Posts: 233

Re: SAS Macro: Catagorizing a Numerical variable

Posted in reply to ehsanmath

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: SAS Macro: Catagorizing a Numerical variable

Posted in reply to ehsanmath

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

or 

select() when()

 

Could be used, or in fact formats with ranges:

proc format;

  value fmt

    1-10="First"

...

;

Regular Contributor
Posts: 233

Re: SAS Macro: Catagorizing a Numerical variable

Posted in reply to ehsanmath

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;

Super User
Posts: 10,023

Re: SAS Macro: Catagorizing a Numerical variable

[ Edited ]
Posted in reply to ehsanmath

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;

Contributor
Posts: 38

Re: SAS Macro: Catagorizing a Numerical variable

[ Edited ]

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

Solution
‎07-20-2016 04:04 AM
Regular Contributor
Posts: 233

Re: SAS Macro: Catagorizing a Numerical variable

Posted in reply to ehsanmath

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;
Contributor
Posts: 38

Re: SAS Macro: Catagorizing a Numerical variable

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 379 views
  • 3 likes
  • 4 in conversation