Desktop productivity for business analysts and programmers

Help: Proc sql - selecting a blank value as a space ' '

Reply
New Contributor
Posts: 2

Help: Proc sql - selecting a blank value as a space ' '

Hi

 

I am having trouble selecting blank values as a space.

 

We have a requirement where we need to provide a file to the business.

Blank values must = ' ' (a space).

 

I am attempting to use the below case statement (which works on MS sql and oracle - I am new to SAS).

 

case when INTERRUPTION_TYPE = '' then ' '
else INTERRUPTION_TYPE end as INTERRUPTION_TYPE

 

The results return the blank values as a blank - no space.

 

When I use the below, it will return an x so I know that my query is picking up the correct values but not returning a space when i want it to.

case when INTERRUPTION_TYPE = '' then 'x' 
else INTERRUPTION_TYPE end as INTERRUPTION_TYPE

 

Please help!

 

Thanks in advance!

 

 

Super User
Super User
Posts: 8,174

Re: Help: Proc sql - selecting a blank value as a space ' '

How are you sending the file to the "business"?   The reason being is that " " is just an empty string in SAS, you can put out data which "looks" like spaces, this for instance puts spaces to the length of the string.

data want;
  set sashelp.class;
  if sex="M" then res=" ";
  else res="X";
  put "Res='" res "'";
run;

So the question is how you are writing data to this "file" you mention.

Super User
Posts: 8,055

Re: Help: Proc sql - selecting a blank value as a space ' '

Keep in mind that SAS character variables are always padded with blanks. Try this:

data test;
length x1 $10 x2 $20;
x1 = '';
x2 = put(x1,$hex20.);
run;

and look at the resulting dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 2

Re: Help: Proc sql - selecting a blank value as a space ' '

Posted in reply to KurtBremser

Thanks for the quick response.

 

Will play around with this as soon as I'm back in the office tomorrow morning.

 

There any chance you could help with a solution in proc sql?

My data step skills are very entry level and we have written the entire script in sql

Super User
Posts: 8,055

Re: Help: Proc sql - selecting a blank value as a space ' '

My data step is only there to illustrate that

'' = ' '

in SAS. Since SAS does not know a varchar type, but only character with a given length (>= 1), all empty/missing character variables will at least contain one blank, so a statement in SQL like

case
  when X = ''
  then ' '
  else X
end as X

won't change anything. You need to redefine your logic in light of this fact.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 114 views
  • 0 likes
  • 3 in conversation