Desktop productivity for business analysts and programmers

Converting a char variable " 00x" to a numeric variable that looks the same: [blank space]00x

Reply
Occasional Contributor
Posts: 6

Converting a char variable " 00x" to a numeric variable that looks the same: [blank space]00x

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)?

Super User
Posts: 13,084

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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.

 

Occasional Contributor
Posts: 6

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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

 

" 001"

" 002"

" 003"

" 004"

" 005"

" 006"

" 007"

 

Super User
Posts: 22,874

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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.;
Occasional Contributor
Posts: 6

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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?

Super User
Posts: 22,874

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp


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

Super User
Posts: 22,874

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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

Occasional Contributor
Posts: 6

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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.

Super User
Posts: 22,874

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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 Smiley Happy

Occasional Contributor
Posts: 6

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

Ok thank you very much for your help.

Super User
Posts: 9,611

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 6

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

Posted in reply to KurtBremser

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

Super User
Posts: 22,874

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

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? 

 

 

Super User
Posts: 13,084

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp


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).

Super User
Super User
Posts: 7,860

Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp

[ Edited ]

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. ;
Ask a Question
Discussion stats
  • 14 replies
  • 214 views
  • 0 likes
  • 5 in conversation