BookmarkSubscribeRSS Feed
djrisks
Barite | Level 11

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".

 

Capture.PNG

 

I have also included an Excel file of the data. Thank you in advance

 

Many thanks,

6 REPLIES 6
ballardw
Super User

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?

Astounding
PROC Star

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.

Reeza
Super User

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. 

 

 

Tom
Super User Tom
Super User

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?

djrisks
Barite | Level 11

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. 

Tom
Super User Tom
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 679 views
  • 0 likes
  • 5 in conversation