BookmarkSubscribeRSS Feed
FrancesU
Calcite | Level 5

Hi I need help in how to fill a text missing field value with a value from a numeric field?  I tried using case statement but that is not working.  Below is my case statement.  This is not working.

     case

        when text_field = ' '

        then put (numeric_field,z9.)

        else text_field

    End as new_field

 

 

 I was adviced to use the coalesce function and that is not working.  I have tried proc sql and data step not working.  My understanding is that when using coalesce function field types have to be the same.  Any help will be greatly appreciated.

 

Thank you.

 

 

6 REPLIES 6
Jagadishkatam
Amethyst | Level 16

As per the code, it seems fine. Could you please provide the log and sample data for better response.

Thanks,
Jag
Reeza
Super User

That's obviously not your actual code, so perhaps post your actual code and log. 

 

Ideally, a sample where the issue is replicated is helpful, especially in a case such as yours, where the code appears correct. 

 

For using COALESCE it would be:

 

COALESCEC(text_field, put(numeric_field, z9.)) as new_field

 

My guess is going to be that your 'missing' value isn't missing. Use a hex format to view the field or COMPRESS anything that may look like a blank but actually has a tab or some other value. 

Astounding
PROC Star

If I'm not mistaken (?), the word THEN does not belong in a CASE statement.  Just remove it (assuming you have built the rest of the SQL code properly).

Jagadishkatam
Amethyst | Level 16
@Astounding but the word THEN is part of the case when syntax in proc sql. Please correct me
Thanks,
Jag
Astounding
PROC Star

Just going by memory ... I will have to test it tomorrow.

 

Looks like memory was faulty here ... THEN is definitely part of a CASE statement.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please clarify what you are doing.  Follow the guidance under post a question - post test data in the form of a datastep, and what you want the output to look like.  Also (important in this case), post your full code for that block, use the code window {i} above post.

From what you have posted we cannot see if you are using datastep or SQL.  At a guess:

Datastep:

data want;
  set have;
  text_field=ifc(text_field=' ',put(numeric_field,z9.),text_field);
/* or */
  text_field=coalescec(text_field,put(numeric_field,z9.));
run;

SQL:

proc sql;
  create table WANT as
  select COALESCE(TEXT_FIELD,put(NUMERIC_FIELD,z9.)) as NEW_FIELD
  from   HAVE;
quit;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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