BookmarkSubscribeRSS Feed
Rhein
Calcite | Level 5

If x is any number from 1 to 9, how to convert a char variable that cointains the following: " 00x" to a numeric variable that keeps the same structure [blank space]00x (in proc sql)?

14 REPLIES 14
ballardw
Super User

Numeric variables in general do not ever contain a leading space. Do all of these values end in X? If not then I doubt we will find a way to do this unless the numeric portion is unique for each character.

I think what might help the most would be to run:

 

Proc sql;

    select distinct variablenamegoeshere

    from datasetnamegoeshere

    ;

quit;

and post the result so we can see the range of values you may be dealing with.

 

Rhein
Calcite | Level 5

I have applied the select distinct statement and I get the following:

 

" 001"

" 002"

" 003"

" 004"

" 005"

" 006"

" 007"

 

Reeza
Super User

Apply a Z3 format. You don't get a leading space but you will get the leading zeroes. 

If you do really, really want a leading space, you can probably create a custom format. 

 

x = input(z, best12.);
format x z3.;
Rhein
Calcite | Level 5

Thank you for your response. However, I really want to put the leading space. Can you please explain to me how to create a custom format to be able to do it?

Reeza
Super User

@Rhein wrote:

Thank you for your response. However, I really want to put the leading space. Can you please explain to me how to create a custom format to be able to do it?


It seems easier to stick with a character variable then, but a Picture format would be likely what you need. 

http://www2.sas.com/proceedings/sugi31/243-31.pdf

Reeza
Super User

Just a heads up, Numbers, by default, are aligned right so you won’t see the space either unless you left align when testing.  

Rhein
Calcite | Level 5

Thanks, very interesting paper. I tried to apply it but still can’t manage ti keep a leading blank. Let me show you what i did:

proc format;

picture formatpic (default=4)

low-high='999'

(prefix=' ');

run;

 

PROC SQL;

CREATE TABLE newtable AS

SELECT input_variable format=formatpic.

FROM input_table;

QUIT;

Where my input variable contained numeric values like: “003”

The idea was to add the prefix “ “, but it won’t add it. The way i test it is by exporting it to a .txt and seeing if the leading blank is there.

 

In the paper, it says 0 print blanks. So, I also tried:

proc format;

picture formatpic (default=4)

low-high='0999';

run;

 

PROC SQL;

CREATE TABLE newtable AS

SELECT input_variable format=formatpic.

FROM input_table;

QUIT;

Where my input variable contained numeric values like: “0003”. But when exporting again I don’t get my leading blank.

Reeza
Super User

Yeah, I tried a bunch of things...couldn't get it either. 

 

Why do you need the blank? Perhaps there's another way to get the same behaviour. 
If you have a valid SAS license (likely with EG) I would consider asking tech support - or waiting to see if someone else can answer it :). 

Rhein
Calcite | Level 5

Ok thank you very much for your help.

Kurt_Bremser
Super User

Your format does work:

proc format;
picture formatpic (default=4)
low-high='0999';
run;

data test;
length x1 $4;
x1 = put(9,formatpic4.);
x2 = put(x1,$hex8.);
run;

proc print data=test noobs;
run;

The resulting output is:

x1        x2

009    20303039

so you can clearly see that the leading blank (hex 20) is there.

Your problem comes when you export the string, instead of a formatted number. SAS will remove leading blanks from strings when writing them to a text file:

data _null_;
set test;
file '$HOME/sascommunity/test.csv' dlm=',';
put x1 x2;
x3 = 9;
put x3 formatpic4.;
run;

The resulting file looks like this:

009,20303039
 009

The string has the leading blanks stripped, but the formatted numeric variable is written as you want.

Rhein
Calcite | Level 5

Thanks, but how do I export it so that it keeps the blanks?

Reeza
Super User

Try a CHAR4 format, or if you want a space, why not explicitly export a space and the number using Z3 if that's your end goal? 

 

 

ballardw
Super User

@Rhein wrote:

Thanks, but how do I export it so that it keeps the blanks?


How are you currently exporting the data? Provide code examples.

There are multiple ways to send data elsewhere and each has advantages and disadvantages. Your specific method may be part of the issue, or the destination file, or how you examine the destination file (note Excel reformats lots of stuff without telling you).

Tom
Super User Tom
Super User

The request does not make any sense. Numbers do not contain spaces.

If you want to put a space into a value then the variable is by definition a character string.

 

Are you trying to produce a report?  If you want to write space plus zero padded numbers then just do that.

put ' ' x z3. ;

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
  • 14 replies
  • 1456 views
  • 0 likes
  • 5 in conversation