topic Re: Create new variable based on values/rules in other columns in SAS Programming

Create new variable based on values/rules in other columns

zimcom — Fri, 02 Nov 2018 20:44:38 GMT

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!!

Re: Programming codes

Reeza — Fri, 02 Nov 2018 20:44:04 GMT

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-data-AKA-generate/ta-p/258712

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

Re: Programming codes

zimcom — Fri, 02 Nov 2018 20:47:07 GMT

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

Thank you @Reeza

Re: Programming codes

Reeza — Fri, 02 Nov 2018 20:54:03 GMT

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.

Re: Programming codes

zimcom — Fri, 02 Nov 2018 21:00:19 GMT

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

Thank you as always @Reeza

Re: Programming codes

Reeza — Fri, 02 Nov 2018 21:07:46 GMT

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.

Re: Create new variable based on values/rules in other columns

ballardw — Fri, 02 Nov 2018 21:12:50 GMT

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.

Re: Create new variable based on values/rules in other columns

zimcom — Fri, 02 Nov 2018 23:34:48 GMT

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.

Re: Create new variable based on values/rules in other columns

Patrick — Sat, 03 Nov 2018 03:17:18 GMT

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-data-AKA-generate/ta-p/258712

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.);

Re: Create new variable based on values/rules in other columns

zimcom — Sat, 03 Nov 2018 13:58:08 GMT

@Patrick Thank you very much for pointing out the direction

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

Re: Create new variable based on values/rules in other columns

heatherjae15 — Sun, 27 Oct 2019 00:43:00 GMT

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;

Re: Create new variable based on values/rules in other columns

heatherjae15 — Sun, 27 Oct 2019 00:56:11 GMT

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.

Re: Create new variable based on values/rules in other columns

Reeza — Sun, 27 Oct 2019 03:05:26 GMT

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.

Re: Create new variable based on values/rules in other columns

Reeza — Sun, 27 Oct 2019 03:14:07 GMT

Please do not post the same question in multiple posts.

Re: Create new variable based on values/rules in other columns

heatherjae15 — Sun, 27 Oct 2019 03:32:41 GMT

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

Re: Create new variable based on values/rules in other columns

Reeza — Sun, 27 Oct 2019 03:34:22 GMT

yes

Re: Create new variable based on values/rules in other columns

heatherjae15 — Sun, 27 Oct 2019 03:46:26 GMT

Please find the log attached

Re: Create new variable based on values/rules in other columns

Reeza — Sun, 27 Oct 2019 17:33:49 GMT

284  */ changing inches to cms*/;
285  data demo2;
286  set mergedemo;
287 If comor_9hgtunit_decode then do;
288  height_cm = comor_9hgt/2.54;
289  end;
290  else do;
291  height_cm =comor_9hgt;
292  end;
293  run;
NOTE: Character values have been converted to numericvalues at the places given by: (Line):(Column).287:4
NOTE: Invalid numeric data, comor_9hgtunit_decode='cm' , atline 287 column 4.

See how the log indicates where the error is?

It says Line 287:4, which happens to be this comor_9hgtunit_decode

Your code is incorrect, it should be checking against a value as I indicated earlier. I believe you're trying to convert inches to cm so check if the value is inches.

If comor_9hgtunit_decode = 'inches' then do;

@heatherjae15 wrote:

Please find the log attached

Also, when you paste the log into a document not everyone can view it - I can't if I'm at work for example. Additionally, when I copied and pasted it back here I had to reformat it. So in the future please, paste only the code and log that has the errors starting from the first error and paste the log directly into the forum, similar to how I did for my response. Use the 6th icon in the editor to insert the log. This makes it much easier and faster to answer your question and our computer don't get clogged with downloads.