If/then with missing data

[GS2]

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

[PaigeMiller]

---

@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-dat...

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

[GS2]

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

[Kurt_Bremser · Accepted solution]

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.

[GS2]

Removing the multiple set statements corrected my problem. Thank you

[hashman]

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

[Tom]

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

[hashman]

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