Categorize variables basing on conditions from other data set

Question

I have two datasets, one of them contains numeric variables, and the other one contains some conditions on which these numeric variables should be split. Let's say, the first dataset would look like this:

var1    var2
1       12
8       4

And the other one:

varname    condition    group
var1       var1 < 5     1
var1       var1 >= 5    2
var2       var2 < 6     1
var2       var2 >= 6    2

Then the output dataset would look like:

var1    var2    var1_cat    var2_cat
1       12      1           2
8       4       2           1

If i had to deal with any of "standard" programming languages, I'd just use a nested iteration. However SAS and it's data step logic is so far quite unfamiliar to me.

Any help will be aprreciated.

How complex do your rules become? That's going to be the hard portion to implement in SAS. SAS doesn't always require doing the conversion, formats allow for customized display of the information. — Reeza

Tom Tom · Accepted Answer · 2016-04-16T21:48:05

Not sure how you would do this in a 'regular language', but in SAS it is a simple matter of code generation. One of the benefits of using an interpreted language.

For example you could turn your "rules" dataset into a series of SQL case statements.

filename code temp;
data _null_;
  set rules end=eof;
  by varname ;
  file code ;
  if _n_=1 then put 'create table want as select *' ;
  newvar=cats(varname,'_cat');
  if first.varname then put ',case' ;
  put '  when (' condition ') then ' group ;
  if last.varname then put ' else . end as ' newvar ;
  if eof then put 'from have;' ;
run;

Now that you have the code in a file just include it.

proc sql;
 %include code / source2 ;
quit;

Categorize variables basing on conditions from other data set

1 Answers