BookmarkSubscribeRSS Feed
Shwam
Calcite | Level 5

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!

 

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

Shwam
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 5664 views
  • 0 likes
  • 3 in conversation