topic Re: Converting a char variable " 00x" to a numeric variable that looks the same: [blank sp in SAS Enterprise Guide

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

Rhein — Tue, 19 Dec 2017 23:45:49 GMT

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

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

ballardw — Wed, 20 Dec 2017 00:12:58 GMT

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.

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

Rhein — Wed, 20 Dec 2017 00:21:47 GMT

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

" 001"

" 002"

" 003"

" 004"

" 005"

" 006"

" 007"

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

Reeza — Wed, 20 Dec 2017 00:43:37 GMT

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

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

Rhein — Wed, 20 Dec 2017 00:51:39 GMT

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?

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

Reeza — Wed, 20 Dec 2017 01:22:20 GMT

@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

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

Reeza — Wed, 20 Dec 2017 01:25:26 GMT

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

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

Tom — Wed, 20 Dec 2017 04:35:05 GMT

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

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

Rhein — Wed, 20 Dec 2017 02:38:36 GMT

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.

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

Reeza — Wed, 20 Dec 2017 02:42:15 GMT

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

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

Rhein — Wed, 20 Dec 2017 03:04:53 GMT

Ok thank you very much for your help.

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

Kurt_Bremser — Wed, 20 Dec 2017 10:06:05 GMT

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.

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

Rhein — Wed, 20 Dec 2017 17:10:32 GMT

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

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

Reeza — Wed, 20 Dec 2017 17:15:15 GMT

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?

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

ballardw — Wed, 20 Dec 2017 18:52:46 GMT

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