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
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 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.
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
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.
Removing the multiple set statements corrected my problem. Thank you
@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.
Actually no need for IF statements at all. Just use the COALESCE() function. Of it the variables are character then use the COALESCEC() function.
@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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: