DATA Step, Macro, Functions and more

SAS SQL - CASE STATEMENT

Reply
New Contributor D7
New Contributor
Posts: 2

SAS SQL - CASE STATEMENT

Hi, I'm using a case else statement to try to create a new variable (source_flag) to fill in for blank for the first_source variable, however it is not populating the new variable definition of 'unknown' when it runs.  Any help would be greately appreciated.

 

proc sql;
create table d02_buyer_source_recency as
select DISTINCT email_id,
/* first source */
first_source,
first_available_source,
CASE when (first_source = ' ' OR first_source = '.') then 'UNKNOWN'
ELSE first_source END as Source_Flag
from oralib.sum_email_list_vw ;
quit;

PROC Star
Posts: 307

Re: SAS SQL - CASE STATEMENT

It's not fully clear without seeing your data, but you could try:

 

CASE first_source WHEN '' THEN 'UNKNOWN'

                              WHEN '.' THEN 'UNKNOWN'

                              ELSE first_source

END AS Source_Flag

New Contributor D7
New Contributor
Posts: 2

Re: SAS SQL - CASE STATEMENT

Posted in reply to collinelliot

Hi, thank you for your recommendation, however when I ran the adjustment still gettting 'blank' variable instead of 'unknown' in the source_flag fields.

 

PROC Star
Posts: 307

Re: SAS SQL - CASE STATEMENT

I assume first_source is character. Are there embedded blanks? Maybe put strip(first_source) for the CASE statement?

Super User
Super User
Posts: 7,050

Re: SAS SQL - CASE STATEMENT

Make sure your character variable is really empty. It might just look empty, but really include non-printing characters like tabs, line feeds, carriage returns, form feeds, non-breaking spaces, nulls.  

 

Also it might look like it has just a period in it, but it really has a period that follows one or move spaces.

CASE when compress(first_source) in (' ','.')  then 'UNKNOWN'
     ELSE first_source 
END as Source_Flag
Ask a Question
Discussion stats
  • 4 replies
  • 1021 views
  • 0 likes
  • 3 in conversation