topic Re: If/then with missing data in SAS Programming

If/then with missing data

GS2 — Wed, 10 Jul 2019 18:14:38 GMT

Using SAS 9.4

I am writing an if/then statement to move data from one variable to another if the data is blank (see below for code). However when I run the code the data is not populating into the new variable. Based on the frequency below I know that their are 149 blank entries. The format for variable 'first_dx' is $CHAR4 so the ' ' method should work to capture the blank entries. Any thoughts would be helpful. Thank you

data work.demog_crstar_pt_level_final;
set work.demog_crstar_pt_level;
if first_dx = ' ' then first_dx = topography_code;
set work.demog_crstar_pt_level;
if first_dx_desc = ' ' then first_dx_desc = topography_code_desc;
set work.demog_crstar_pt_level;
if first_dx_date = . then first_dx_date = date_of_diagnosis;

run;

first_dx Frequency Percent Frequency Percent
149 73.76 149 73.76
C619 46 22.77 195 96.53
C669 1 0.50 196 97.03
C672 2 0.99 198 98.02
C674 1 0.50 199 98.51
C678 2 0.99 201 99.50
C679 1 0.50 202 100.00

Re: If/then with missing data

PaigeMiller — Wed, 10 Jul 2019 18:37:21 GMT

@GS2 wrote:

first_dx Frequency Percent Frequency Percent
149 73.76 149 73.76
C619 46 22.77 195 96.53
C669 1 0.50 196 97.03
C672 2 0.99 198 98.02
C674 1 0.50 199 98.51
C678 2 0.99 201 99.50
C679 1 0.50 202 100.00

This text representation of your data set isn't really specific enough for debugging. Can you provide the data set following these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Also your code has lots of variables that are not shown in this small data set. Please explain further.

Re: If/then with missing data

Kurt_Bremser — Wed, 10 Jul 2019 18:48:36 GMT

Why do you read the same observation from demog_crstar_pt_level three times in each iteration of the data step? Because that's what the three set statements do in this code:

data work.demog_crstar_pt_level_final;
set work.demog_crstar_pt_level;
if first_dx = ' ' then first_dx = topography_code;
set work.demog_crstar_pt_level; /* any change to first_dx will be reversed by this */
if first_dx_desc = ' ' then first_dx_desc = topography_code_desc;
set work.demog_crstar_pt_level; /* any change to first_dx_desc will be reversed by this */
if first_dx_date = . then first_dx_date = date_of_diagnosis;
/* only this last change will have an effect */
run;

Please supply usable example data from your dataset in a data step with datalines.

Re: If/then with missing data

GS2 — Wed, 10 Jul 2019 19:01:54 GMT

I was showing the results of a frequency to illustrate my point that their are missing data that I should be able to write code to capture. That is why their are variable in my code that you do not see. Thank you for the input

Re: If/then with missing data

GS2 — Wed, 10 Jul 2019 19:04:08 GMT

Removing the multiple set statements corrected my problem. Thank you

Re: If/then with missing data

hashman — Wed, 10 Jul 2019 22:08:46 GMT

@GS2 :

Methinks you should mark the post by @Kurt_Bremser as a solution since it pinpoints the problem and suggests a way to eliminate it: Your extraneous SET statements cause the conditional assignments to change the assigned values. In other words, you should get rid of all the SET statements but the very first, and then the code should work. Also, you can somewhat streamline and homogenize your code by using the MISSING or CMISS function insensitive to the data type instead of testing for type-specific missing value literals, for example:

data work.demog_crstar_pt_level_final ;                               
  set work.demog_crstar_pt_level;                                     
  if cmiss (first_dx     ) then first_dx      = topography_code ;     
  if cmiss (first_dx_desc) then first_dx_desc = topography_code_desc ;
  if cmiss (first_dx_date) then first_dx_date = date_of_diagnosis ;   
run ;

Please do NOT mark my post as a solution - it belongs to @Kurt_Bremser.

Kind regards

Paul D.

Re: If/then with missing data

Tom — Wed, 10 Jul 2019 22:13:06 GMT

Actually no need for IF statements at all. Just use the COALESCE() function. Of it the variables are character then use the COALESCEC() function.

Re: If/then with missing data

hashman — Wed, 10 Jul 2019 23:02:45 GMT

@Tom :

True. However, the need to use two functions depending on the data type makes code more error-prone and less robust. IF is a small penalty for the data type insensitivity of CMISS or MISSING. On the other hand, if SQL were used instead, where COALESCE is type insensitive, I'd more than agree that using it would be preferable to using the CASE clause.