Hello,
I would like to run the code that is in the CTC_ALGORITHM variable for each observation in my dataset. Can you help me please? I did try call execute, but that only executed the code based on the last observation in this dataset. You can see that the first row has a value of 9.93 in the AVAL column (or AVAL_), so I would expect the CTCTERM variable to have a value of "Hypercalcemia".
I have also included an Excel file of the data. Thank you in advance
Many thanks,
Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
To create datastep code from a data set Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
You are going to have to provide a bit more of what is going on.
WHY does the algorithm change from record to record? The algorithm changing is not typical behavior unless there are other variables that are used to set those boundary values. In which case where are they?
You will have to show us how you are applying CALL EXECUTE.
Most likely, you are applying every value of CTC_ALGORITHM in the same DATA step. In that case, each CTC_ALGORITHM replaces the results that were calculated previously. Examining the log should reveal if that's the case.
The CALL EXECUTE second example is what you're trying to do.
Although you've shown us the data, show us the code since that's what you're having issues with.
Post the code, log especially if there are notes or errors.
You haven't given us enough information to tell if CALL EXECUTE is even appropriate for your problem. It does not look right given the code you posted in the Excel file.
SUBJID CTC_ALGORITHM AVAL
1 If AVAL<8.2 then CTCTERM = "Hypocalcemia"; Else if AVAL>9.6 then CTCTERM = "Hypercalcemia"; Else CTCTERM = ""; 9.93987968
1 If AVAL<8.6 then CTCTERM = "Hypocalcemia"; Else if AVAL>10 then CTCTERM = "Hypercalcemia"; Else CTCTERM = ""; 9.2184368
1 If AVAL<8.4 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.2 then CTCTERM = "Hypercalcemia"; Else CTCTERM = ""; 9.2184368
1 If AVAL<8.8 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.8 then CTCTERM = "Hypercalcemia"; Else CTCTERM = ""; 8.73747488
What data are you going to run those statements against? The logic of the statements conflict so how do you know which one to run for a given observation?
Hi Below, is the code to generate the SAS dataset.
data TEST;
infile datalines dsd truncover;
input USUBJID:$22. PARAMCD:$8. PARAM:$200. AVAL:32. SEX:$1. AGE:32. AGELO:BEST9. AGEHI:BEST9. A1HI:BEST11. A2HI:BEST9. A3HI:BEST9. A4HI:BEST9. A1LO:BEST11. A2LO:BEST11. A3LO:BEST11. A4LO:BEST11. CTC_ALGORITHM:$176.;
datalines4;
1,CAF04C,SERUM Calcium (mg/dL),9.93987968,M,44,91,999,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.2 then CTCTERM = ""Hypocalcemia""; Else if AVAL>9.6 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),9.2184368,M,44,18,59,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.6 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),9.2184368,M,44,13,17,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.4 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10.2 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),8.73747488,M,44,2,12,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.8 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10.8 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),8.8176352,M,44,60,90,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.8 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10.2 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),8.33667328,M,44,60,90,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.8 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10.2 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),8.6172344,M,44,60,90,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.8 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10.2 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
1,CAF04C,SERUM Calcium (mg/dL),8.016032,M,44,60,90,11.5,12.5,13.5,13.5,8,7,6,6,"If AVAL<8.8 then CTCTERM = ""Hypocalcemia""; Else if AVAL>10.2 then CTCTERM = ""Hypercalcemia""; Else CTCTERM = """";"
;;;;
run;
Although, I believe I need to look into the creation of the dataset further. This is because I thought the dataset was merged properly before, and that is why I wanted the IF..THEN step done for each row separately. But I've now seen that I now need to merge in the file appropriately and take into account the age and gender of the patients. That is why there were so many different conditions in the Excel file before, because they take into account the gender and the age of the patient too. Althought in this example, there is only one age, which is 44, and one gender, so theoretically, only one condition should be used.
Also the code below, was the code which was being executed before (in call execute). I probably need to also add the age and gender conditions into the code now:
data lb_ctc_test;
set lb_ctc;
if paramcd="CAF04C" then do;
If AVAL<8.2 then CTCTERM = "Hypocalcemia";
Else if AVAL>9.6 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.8 then CTCTERM = "Hypocalcemia";
Else if AVAL>10.2 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.6 then CTCTERM = "Hypocalcemia";
Else if AVAL>10 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.4 then CTCTERM = "Hypocalcemia";
Else if AVAL>10.2 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.8 then CTCTERM = "Hypocalcemia";
Else if AVAL>10.8 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.2 then CTCTERM = "Hypocalcemia";
Else if AVAL>9.6 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.6 then CTCTERM = "Hypocalcemia";
Else if AVAL>10 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.4 then CTCTERM = "Hypocalcemia";
Else if AVAL>10.2 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.8 then CTCTERM = "Hypocalcemia";
Else if AVAL>10.8 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
if paramcd="CAF04C" then do;
If AVAL<8.8 then CTCTERM = "Hypocalcemia";
Else if AVAL>10.2 then CTCTERM = "Hypercalcemia";
Else CTCTERM = "";;
end;
run;
Thank you.
I would make a RULES datasets that does NOT depend on any particular patients values. So for the data you posted it might look like this depending of whether those cut off values depend on the patient's GENDER or not.
Obs PARAMCD PARAM SEX AGELO AGEHI 1 CAF04C SERUM Calcium (mg/dL) M 2 12 2 CAF04C SERUM Calcium (mg/dL) M 13 17 3 CAF04C SERUM Calcium (mg/dL) M 18 59 4 CAF04C SERUM Calcium (mg/dL) M 60 90 5 CAF04C SERUM Calcium (mg/dL) M 91 999 Obs CTC_ALGORITHM 1 If AVAL<8.8 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.8 then CTCTERM = "Hypercalcemia"; Else 2 If AVAL<8.4 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.2 then CTCTERM = "Hypercalcemia"; Else 3 If AVAL<8.6 then CTCTERM = "Hypocalcemia"; Else if AVAL>10 then CTCTERM = "Hypercalcemia"; Else CT 4 If AVAL<8.8 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.2 then CTCTERM = "Hypercalcemia"; Else 5 If AVAL<8.2 then CTCTERM = "Hypocalcemia"; Else if AVAL>9.6 then CTCTERM = "Hypercalcemia"; Else C
You can then use a data step like this to generate code.
filename code temp;
data _null_;
set rules end=eof;
by paramcd ;
file code ;
if first.paramcd then
put '* ' paramcd param ';'
/ 'if ' paramcd= :$quote. 'then do;'
;
put ' if ' sex= $:quote. 'and ' agelo '<= age <=' agehi 'then do;'
/ ' ' ctc_algorithm
/ ' end;'
;
if not last.paramcd then put ' else' @ ;
else put 'end;' ;
run;
data lab2 ;
set lab1 ;
%include code / source2 ;
run;
So then you end up with code like this:
2631 data lab2 ;
2632 set lab1 ;
2633 %include code / source2 ;
NOTE: %INCLUDE (level 1) file CODE is file C:\Users\...\#LN00766.
2634 +* CAF04C SERUM Calcium (mg/dL) ;
2635 +if PARAMCD="CAF04C" then do;
2636 + if SEX="M" and 2 <= age <=12 then do;
2637 + If AVAL<8.8 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.8 then CTCTERM = "Hypercalcemia";
2637!+ Else CTCTERM = "";
2638 + end;
2639 + else if SEX="M" and 13 <= age <=17 then do;
2640 + If AVAL<8.4 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.2 then CTCTERM = "Hypercalcemia";
2640!+ Else CTCTERM = "";
2641 + end;
2642 + else if SEX="M" and 18 <= age <=59 then do;
2643 + If AVAL<8.6 then CTCTERM = "Hypocalcemia"; Else if AVAL>10 then CTCTERM = "Hypercalcemia";
2643!+Else CTCTERM = "";
2644 + end;
2645 + else if SEX="M" and 60 <= age <=90 then do;
2646 + If AVAL<8.8 then CTCTERM = "Hypocalcemia"; Else if AVAL>10.2 then CTCTERM = "Hypercalcemia";
2646!+ Else CTCTERM = "";
2647 + end;
2648 + else if SEX="M" and 91 <= age <=999 then do;
2649 + If AVAL<8.2 then CTCTERM = "Hypocalcemia"; Else if AVAL>9.6 then CTCTERM = "Hypercalcemia";
2649!+Else CTCTERM = "";
2650 + end;
2651 +end;
NOTE: %INCLUDE (level 1) ending.
2652 run;
You could probable also move the LLN (8.2 for example) and ULN (9.6 for example) and labels out of the "code" and into variables in the RULES dataset and generate the code that you currently have in the CTC_ALGORITHM variable.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.