Create new variable based on values/rules in other columns

[zimcom]

Hi Community,

Happy Friday! I need to write a piece of code to do the following:

If weight_unit = ‘kg’ and weight is between Low and high in the table below, then dose_volume = value in column 3  

Low	High
≥45	≤49	6
>49	≤54	7
>54	≤59	7
>59	≤64	8
>64	≤69	9
>69	≤74	9
>74	≤79	10
>79	≤84	11
>84	≤89	11
>89	≤94	12
>94	≤99	13
>99	≤120	13.5

If weight_unit = ‘lbs’ and weight is between Low and high below, then dose_volume = value in column 3  

Low	High
≥ 99	≤108	6
>108	≤119	7
>119	≤130	7
>130	≤141	8
>141	≤152	9
>152	≤163	9
>163	≤174	10
>174	≤185	11
>185	≤196	11
>196	≤207	12
>207	≤218	13
>218	≤264	13.5

 Any good code suggestion?

THANKS!!

[Reeza]

What have you tried so far? What are the formats and types on the data, specifically the low/high variables? Can you please provide data as a data step as illustrated here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And ideally, better subjects please. I'll update this one, but please do try to do this in the future.

[zimcom]

I can sort out the data type extra, I just need the algorithm if this helps...

Thank you @Reeza

[Reeza]

Use SCAN or COMPRESS to remove the greater than and less than signs. Create formats using CTNLIN driven from the data sets and then you can use the format in any data step.

[zimcom]

could you show me an example, please, I have never use SCAN function and never create formats using CTNLIN

Thank you as always @Reeza

[Reeza]

If you need code could you please answer some of my original questions, specifically, sample data as a data step. Otherwise, I recommend reading a paper called 'Proc formats, not just another pretty face' for help.

[ballardw]

Is that one data set or two? Also where is the actual data with something that has the UNITS values you depend on using for calculation?

Personally I would either use that information to create informats or right SELECT/WHEN/END blocks of code than attempt to deal with parsing out the ≥ and ≤ from data.

[zimcom]

I have a list of patient Id, weight (numeric), with weight_unit captured in either 'kg' or 'lbs',

so first I need to check the weight_unit through each observation in the list to decide which category I should compare to and then based on their weight assign the dose_volume for each patient. The weight categories in the table ( "≥ and ≤") is not in the dataset, they are form the Protocol. Sorry for the confusion  

Thank you @ballardw @Reeza If you could show me a short exmaple, that would be greatly appreciated.

[Patrick · Accepted solution]

If you want tested code then you need to provide sample data.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

You are after some sort of grouping. Using SAS Formats or Informats is a great way to do so (creating groups based on the tables you've posted). Look up Proc Format if you don't know how this is done.

Below some pseudo code how this could work with SAS Informats:

if weight_unit='lbl' then dose_volume=input(weight, lbl_doses.);
else  if weight_unit='kg' then dose_volume=input(weight, kg_doses.);

Alternatively first convert everything to kg and then use a single informat.

if weight_unit='lbl' then weight_in_kg=weight*<conversion factor>;
else weight_in_kg=weight;
dose_volume=input(weight, kg_doses.);

[zimcom]

 @Patrick Thank you very much for pointing out the direction

After Proc format into groups and it worked out as I expected, Thank you!

[heatherjae15]

I am trying to change all of my data into cms when the data has some in inches and some in cms.  I have these variables

comor_9hgt  variable has actual measurements

comor_9hgtunit_decode = cm =1  or inches=2

comor_9hgtunit   says cm or inches

This is what I have tried

data demo2;
set mergeddemo;
If comor_9hgtunit_decode then do;
height_cm = comor_9hgt/2.54;
end;
else do;
height_cm =comor_9hgt;
end;
run;

[heatherjae15]

I am trying to change all of my data into cms when the data has some in inches and some in cms.  I have these variables

comor_9hgt  variable has actual measurements

comor_9hgtunit_decode = cm =1  or inches=2

comor_9hgtunit   says cm or inches

This is what I have tried

data demo2;
set mergeddemo;
If comor_9hgtunit_decode then do;
height_cm = comor_9hgt/2.54;
end;
else do;
height_cm =comor_9hgt;
end;
run;

I am getting this error message.  The data set WORK.DEMO2 may be incomplete. When this
step was stopped there were 0 observations and 3
variables.
WARNING: Data set WORK.DEMO2 was not replaced because this step
was stopped.

[Reeza]

Show the full log. Where the error occurs tells you.

I will say that your IF condition is incorrect.
SAS evaluates any value > 0 as true, so 1/2 would be true and converted, you want to test if the variable is equal to 1 or 2.

[Reeza]

Please do not post the same question in multiple posts.

[heatherjae15]

The second question was by accident sorry.  Do you want to see the full log?