Solved: Re: IF/THEN

kristiepauly · Posted 08-02-2022 03:59 PM

I have a large .xlsx dataset with with many variables including follow up visit data. In excel, I transposed all the follow up data to line up vertically (e.g. some Subject ID's have 1 observation line because they have no follow up data and some have many observations with follow up data). I need to take the character variable iq_diagnosis (0=normal, 1=abnormal) and if that is 0 then I need to grab the value in character variable diag_iq_norm_range (0-4) and output what each of those values represents (e.g. 0= IQ86-100, 1=IQ101-115 etc). Else if iq_diagnosis is 1 then I need to use the values for the develop_delay category (1-4) and output what each of those values represent (1= Borderline (IQ 71-85) 2=Mild (IQ51-70) etc). I used proc format to apply the wording to the values and then used IF/THEN statement but I don't get anything in my "iq" column.

Proc format;
	value $diag_iq_norm_range 	" " = "Missing"
								"0" = "86 - 100"
								"1" = "101 - 115"
								"2" = "116 - 129"
								"3" = ">= 130"
								"4" = "Not available";	
	value $diag_iq " " = "Missing"
					"0" = "Normal"
					"1"="Abnormal"; 						
	value $diag_global_dev_delay
							"." = "Missing"
							"1" = "Borderline (IQ=71-85)"
							"2" = "Mild (IQ=51-70)"
							"3" = "Moderate (IQ =36-50)"	
							"4" = "Severe to Profound (IQ<35)";
run;

Data datasets.REDCap_PDE0;
Set import;
IF iq_diagnosis = "0" then IQ = diag_iq_norm_range;
ELSE IF iq_diagnosis = "1" then IQ = diag_global_dev_delay;
ELSE iq_diagnosis = "Missing";
Run;

Proc print data=datasets.redcap_pde0;
run;

Reeza · Posted 08-02-2022 06:14 PM

Data datasets.REDCap_PDE0;
length IQ $50.;
Set datasets.REDCap_PDE0;
IF diag_iq_norm_range = " " then IQ = "Missing";
else IF diag_iq = "0" then IQ = put(diag_iq_norm_range, $diag_iq_norm_range.);
else IF diag_iq = "1" then IQ = put(diag_global_dev_delay, $diag_global_dev_delay.);
ELSE IQ = "Missing";
Run;

You need the ELSE in the previous IF statements. Otherwise the last IF/ELSE go together and overwrite everything in the first two statements.

You should also be including diag_global_dev_delay in your output to check the mapping.

View solution in original post

Tom · Posted 08-02-2022 04:10 PM

Do you actually have all THREE of the variables you mentioned in your code? iq_diagnosis, diag_iq_norm_range, and diag_global_dev_delay.

Why do the first two branches of your IF/THEN/ELSE logic modify the IQ variable and the third one modify the IQ_DIAGNOSIS variable instead?

Why did you include the FORMAT definitions? Nothing in the other code you showed is using those format for anything. Did you want to use the formats? How did you want to use the formats? Did you want to use the formats to decode some value stored in some vairable to the strings the formats would display for those values? If so what is the name of the variables that has the values one digit character strings the formats are defined to decode?

kristiepauly · Posted 08-02-2022 04:39 PM

Do you actually have all THREE of the variables you mentioned in your code? iq_diagnosis, diag_iq_norm_range, and diag_global_dev_delay. Yes

Why do the first two branches of your IF/THEN/ELSE logic modify the IQ variable and the third one modify the IQ_DIAGNOSIS variable instead? IQ is a new variable that I made using the IF/THEN statement so what i'm trying to say is if Iq_diagnosis is 0 then IQ = the single digit value found in iq_diag_norm_range, but if iq_diagnosis is 1 then IQ will = the single digit value that is found in diag_global_dev_delay. There are many blanks for iq_diagnosis so I set that as the last ELSE statement. I then applied a format using proc format for the values that filled the IQ variable, if it had worked. I don't know how else to display the wording with a numerical value other than proc format.

character variable iq_diagnosis (0=normal, 1=abnormal)

diag_iq_norm_range (0-4, where 0= IQ86-100, 1=IQ101-115 etc).

develop_delay category (1-4, where 1= Borderline (IQ 71-85) 2=Mild (IQ51-70) etc).

Reeza · Posted 08-02-2022 04:57 PM

Use PUTC to convert the variables using the formats.

It seems like you want to create a new variable IQ, if this does not work, please post the EXACT log, excluding data.

Proc format;
	value $diag_iq_norm_range 	" " = "Missing"
								"0" = "86 - 100"
								"1" = "101 - 115"
								"2" = "116 - 129"
								"3" = ">= 130"
								"4" = "Not available";	
	value $diag_iq " " = "Missing"
					"0" = "Normal"
					"1"="Abnormal"; 						
	value $diag_global_dev_delay
							"." = "Missing"
							"1" = "Borderline (IQ=71-85)"
							"2" = "Mild (IQ=51-70)"
							"3" = "Moderate (IQ =36-50)"	
							"4" = "Severe to Profound (IQ<35)";
run;

Data datasets.REDCap_PDE0;

Set import; *note this uses your original data set, not one where you did the previous calculations on - if you want to carry forward changes use the previously created data set not your original data set;

length IQ $50.;
IF iq_diagnosis = "0" then IQ = putc(diag_iq_norm_range, $diag_iq_norm_range.);
ELSE IF iq_diagnosis = "1" then IQ = putc(diag_global_dev_delay, $diag_global_dev_delay.);
*this was referring to IQ_DIAGNOSIS but you seem to want to create IQ variable so assuming that is a logical mistake;
ELSE IQ = "Missing";


Run;

@kristiepauly wrote:

I have a large .xlsx dataset with with many variables including follow up visit data. In excel, I transposed all the follow up data to line up vertically (e.g. some Subject ID's have 1 observation line because they have no follow up data and some have many observations with follow up data). I need to take the character variable iq_diagnosis (0=normal, 1=abnormal) and if that is 0 then I need to grab the value in character variable diag_iq_norm_range (0-4) and output what each of those values represents (e.g. 0= IQ86-100, 1=IQ101-115 etc). Else if iq_diagnosis is 1 then I need to use the values for the develop_delay category (1-4) and output what each of those values represent (1= Borderline (IQ 71-85) 2=Mild (IQ51-70) etc). I used proc format to apply the wording to the values and then used IF/THEN statement but I don't get anything in my "iq" column.
Proc format;
	value $diag_iq_norm_range 	" " = "Missing"
								"0" = "86 - 100"
								"1" = "101 - 115"
								"2" = "116 - 129"
								"3" = ">= 130"
								"4" = "Not available";	
	value $diag_iq " " = "Missing"
					"0" = "Normal"
					"1"="Abnormal"; 						
	value $diag_global_dev_delay
							"." = "Missing"
							"1" = "Borderline (IQ=71-85)"
							"2" = "Mild (IQ=51-70)"
							"3" = "Moderate (IQ =36-50)"	
							"4" = "Severe to Profound (IQ<35)";
run;

Data datasets.REDCap_PDE0;
Set import;
IF iq_diagnosis = "0" then IQ = diag_iq_norm_range;
ELSE IF iq_diagnosis = "1" then IQ = diag_global_dev_delay;
ELSE iq_diagnosis = "Missing";
Run;

Proc print data=datasets.redcap_pde0;
run;

kristiepauly · Posted 08-02-2022 05:31 PM

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         Proc format;
 70         
 70       !  value $diag_iq_norm_range " " = "Missing"
 71         "0" = "86 - 100"
 72         "1" = "101 - 115"
 73         "2" = "116 - 129"
 74         "3" = ">= 130"
 75         "4" = "Not available";
 NOTE: Format $DIAG_IQ_NORM_RANGE is already on the library WORK.FORMATS.
 NOTE: Format $DIAG_IQ_NORM_RANGE has been output.
 75       !                               
 76         
 76       !  value $diag_iq " " = "Missing"
 77         "0" = "Normal"
 78         "1"="Abnormal";
 NOTE: Format $DIAG_IQ is already on the library WORK.FORMATS.
 NOTE: Format $DIAG_IQ has been output.
 78       !                      
 79         
 79       !  value $diag_global_dev_delay
 80         "." = "Missing"
 81         "1" = "Borderline (IQ=71-85)"
 82         "2" = "Mild (IQ=51-70)"
 83         "3" = "Moderate (IQ =36-50)"
 84         "4" = "Severe to Profound (IQ<35)";
 NOTE: Format $DIAG_GLOBAL_DEV_DELAY is already on the library WORK.FORMATS.
 NOTE: Format $DIAG_GLOBAL_DEV_DELAY has been output.
 85         run;
 
 NOTE: PROCEDURE FORMAT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              253.68k
       OS Memory           27304.00k
       Timestamp           08/02/2022 09:26:22 PM
       Step Count                        181  Switch Count  0
       Page Faults                       0
       Page Reclaims                     16
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           48
       
 
 86         
 87         
 88         Data datasets.REDCap_PDE0;
 89         length IQ $5.;
 90         Set datasets.REDCap_PDE0;
 91         IF diag_iq = "0" then IQ = putc(diag_iq_norm_range, $diag_iq_norm_range.);
                                                                 ____________________
                                                                 386
                                                                 200
                                                                 76
 92         ELSE IF diag_iq = "1" then IQ = putc(diag_global_dev_delay, $diag_global_dev_delay.);
                                                                         _______________________
                                                                         386
                                                                         200
                                                                         76
 ERROR 386-185: Expecting an arithmetic expression.
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 ERROR 76-322: Syntax error, statement will be ignored.
 
 93         ELSE IQ = "Missing";
 94         Run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set DATASETS.REDCAP_PDE0 may be incomplete.  When this step was stopped there were 0 observations and 66 
          variables.
 WARNING: Data set DATASETS.REDCAP_PDE0 was not replaced because this step was stopped.
 NOTE: DATA statement used (Total process time):
       real time           0.01 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1867.15k
       OS Memory           28464.00k
       Timestamp           08/02/2022 09:26:22 PM
       Step Count                        182  Switch Count  0
       Page Faults                       0
       Page Reclaims                     270
       Page Swaps                        0
       Voluntary Context Switches        21
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           8
       
 
 95         
 96         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 106        
 User: u46539590

@Reeza

Reeza · Posted 08-02-2022 05:36 PM

My mistake, think you need quotes there.

Data datasets.REDCap_PDE0;

Set import; *note this uses your original data set, not one where you did the previous calculations on - if you want to carry forward changes use the previously created data set not your original data set;

length IQ $50.;
IF iq_diagnosis = "0" then IQ = putc(diag_iq_norm_range, '$diag_iq_norm_range');
ELSE IF iq_diagnosis = "1" then IQ = putc(diag_global_dev_delay, '$diag_global_dev_delay');
*this was referring to IQ_DIAGNOSIS but you seem to want to create IQ variable so assuming that is a logical mistake;
ELSE if missing(iq_diagnosis) then IQ = "Missing";
else IQ = "CHECKME"; *this means something is wrong;


Run;

Reeza · Posted 08-02-2022 05:38 PM

Actually you probably can just use put there...the PUTC is overkill ... something like:

IQ = put(diag_iq_norm_range, $diag_iq_norm_range.);

kristiepauly · Posted 08-02-2022 06:06 PM

I changed the PUTC to PUT and removed the ELSE in the second statement which mostly works. I have some missing values for diag_iq_norm_range so I tried to code that in the same step but it's not giving me the "missing" label in iq.

Data datasets.REDCap_PDE0;
length IQ $50.;
Set datasets.REDCap_PDE0;
IF diag_iq_norm_range = " " then IQ = "Missing";
IF diag_iq = "0" then IQ = put(diag_iq_norm_range, $diag_iq_norm_range.);
IF diag_iq = "1" then IQ = put(diag_global_dev_delay, $diag_global_dev_delay.);
ELSE IQ = "Missing";
Run;

proc print data=datasets.redcap_pde0;
var subject_id diag_iq diag_iq_norm_range IQ;
run;

This is what I get:

Reeza · Posted 08-02-2022 06:14 PM

Data datasets.REDCap_PDE0;
length IQ $50.;
Set datasets.REDCap_PDE0;
IF diag_iq_norm_range = " " then IQ = "Missing";
else IF diag_iq = "0" then IQ = put(diag_iq_norm_range, $diag_iq_norm_range.);
else IF diag_iq = "1" then IQ = put(diag_global_dev_delay, $diag_global_dev_delay.);
ELSE IQ = "Missing";
Run;

You need the ELSE in the previous IF statements. Otherwise the last IF/ELSE go together and overwrite everything in the first two statements.

You should also be including diag_global_dev_delay in your output to check the mapping.

kristiepauly · Posted 08-02-2022 06:28 PM

Worked perfectly. I verified diag_global_dev_delay was mapping correctly. Thanks.

SAS Innovate 2025: Register Now