Create a new variable

[Xinhui]

Hi there 

I have a variable "dd", data range is from -15 to 90, 

I want to create a new variable T, which if DD from 0- -5 t would be -1

-5--10 t would be -2  

0-5  t would be 1 

6-10 t would be 2 

and so on.

But I don't know how to code. 

Please help me to figure out the solusion.

Thanks in advance

[PeterClemmensen]

You can either create a format is you have many ranges to be mappes. Otherwise, simply use if-then-else logic and use this as a template

data have;
   do i=1 to 10;
      dd=ceil(rand('uniform')*20);
      output;
   end;
run;

data want;
    set have;
    if      0 <= dd <  5  then t=-1;
    else if 5 <= dd <= 10 then t=0;
    else                       t=1;
run;

[Xinhui]

DATA T;
SET FULL;
if 15 <= dd < 10 then t=-3;
else if 10 <= dd < 5 then t=-2;
else if 5 <= dd < 0 then t=-1;
else if 0 <= dd < -5 then t= 1;
else if -5 <= dd < -10 then t= 2;
else if -10 <= dd < -15 then t= 3;
else if -15 <= dd < -20 then t= 4;
else if -20 <= dd < -25 then t= 5;
else if -25 <= dd < -30 then t= 6;
else if -30 <= dd < -35 then t= 7;
else if -35 <= dd < -40 then t= 8;
else if -40 <= dd < -45 then t= 9;
else if -45 <= dd < -50 then t= 10;
else if -50 <= dd < -55 then t= 11;
else if -55 <= dd < -60 then t= 12;
else if -60 <= dd < -65 then t= 13;
else if -65 <= dd < -70 then t= 14;
else if -70 <= dd < -75 then t= 15;
else if -75 <= dd < -80 then t= 16;
else if -80 <= dd < -85 then t= 17;
else if -85 <= dd < -90 then t= 18;
else t=.;
run;

I tried this code, not work.

DD is from -90 to 15

[PeterClemmensen]

What do you mean when you say 'does not work'? Does it yield and error? Please do not make us guess 🙂

However, here is a hint. Look at this code

if 15 <= dd < 10 then t=-3;

This can never be true. A number can not be less than 10 and larger than or equal to 15. The error is in your ranges.

[gamotte]

Hello,

Here is an example using a format created with cntlin option.

data ctrl;
    fmtname="xinhui";
    type="n";
    do start=-90 to 10 by 5;
        end=start+5;
        if end>0 then label=put(-end/5, best.);
        else label=put(-start/5, best.);
        output;
    end;
    label=".";
    hlo="O";
    output;
run;

proc format cntlin=ctrl;
run;

data have;
    input x;
    cards;
-25
-30
12
8
-49
-67
35
;
run;

proc print data=have;
    format x xinhui.;
run;

[ballardw]

You code such as

else if -85 <= dd < -90 then t= 18;

Is never true. -95 is SMALLER than -85, not larger. So you cannot have a value that is both larger than -85 and less than -90.

[ed_sas_member]

Hi @Xinhui 

you can try this code

data want;
	set full;
	
	do i=-3 to -1;
		if (5*i) <= dd <  (5*i)+5 then t=i;
	end;
	
	do i=1 to 18;
		if (5*i)-5 < dd <=  (5*i) then t=i;
	end;
	
	if dd=0 then t=1;
	
	drop i;
run;

However, as said by @PeterClemmensen, you need to be very clear on what are the bounds for each category of t, and whether lower / upper bounds are included or not (< or <=).

e.g. I understand that you want the lower bound to be included when dd is negative [-15;-10] [-10;-5] etc.

but it should not be the case when dd is positive: ]5;10] ]10;15] etc.  exceptionalities for the first category : [0;1]

Is that right?

Best,

[Astounding]

Depending on how you answer the boundary questions ... what should T be when dd=0?  when dd=-5? ... here is one approach:

if dd=0 then t=0;
else if dd > 0 then t = ceil(dd/5);
else t = floor(dd/5);