topic Re: Help: Proc sql - selecting a blank value as a space ' ' in SAS Enterprise Guide

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

Shwam — Tue, 14 Nov 2017 13:42:47 GMT

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!

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

RW9 — Tue, 14 Nov 2017 13:53:46 GMT

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.

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

Kurt_Bremser — Tue, 14 Nov 2017 14:10:11 GMT

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.

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

Shwam — Tue, 14 Nov 2017 14:37:28 GMT

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

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

Kurt_Bremser — Thu, 16 Nov 2017 07:35:18 GMT

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.