BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi, I just run into another problem about data conversion -

I want to create a new numeric variable VAR_NEW to equal an existing SAS variable VAR_OLD. However, the numbers contained in VAR_OLD have embedded commas, such as "1,026", "3,085", which makes it essentially in a string format aligned in the middle of the variable column.

As a result, the value of VAR_NEW becomes missing whenever a VAR_OLD value is greater than 999. So, what is the easy way to generate VAR_NEW without the missing value issue?

Thanks a lot!

Message was edited by: SASLooker Message was edited by: SASLooker
7 REPLIES 7
GertNissen
Barite | Level 11
If I understand your question correct - try

data test;
set data;
format var_new 8.;
var_new = input(var_old,best.);
run;
deleted_user
Not applicable
Hi Geniz,

Thanks for the suggestion. I tried that, but that makes var_new entirely into missing values.

But following your direction, I came across this, and it worked perfectly -

data test;
set data;
var_new = input(var_old, comma8.);
run;

Although the result is satisfactory, I am not so sure if using COMMAw.d this way is technically correct, or I just got lucky. Because the doumentation I found about COMMAw.d typically refers to reading external file rather than to converting a SAS internal data file. So, I'd like to hear the others' confirmation or verification.
Patrick
Opal | Level 21
The COMMAw.d and other informats tell SAS how to interprete a string for converting it into a SAS number. This string can of course be stored in a SAS character variable.
SuperSas1
Calcite | Level 5

If you have a comma in a text field using the comma8. input statement works; the best will not format it correctly and continue with the same problem.  

Tom
Super User Tom
Super User

@SuperSas1 wrote:

If you have a comma in a text field using the comma8. input statement works; the best will not format it correctly and continue with the same problem.  


Not sure why you are responding to an ancient thread....

 

The INPUT() function does not mind if the width used on the informat is larger than the length of the string being read, so in general you should just use the maximum width that the informat supports.  For COMMA that is 32.

number = input(string,comma32.);

Also notice that do not want to include a decimal width on an informat except for the rare occasion when you know the strings being read were created explicitly without the decimal point and want SAS to divide by that power of 10 to insert one.  This is normally not done for strings that include commas.

 

Note also the BEST is the name of a FORMAT.  Its name comes from its attempt to find the "best" way to display a number in a limited number of characters.  There is no corresponding concept of the "best" way to store a string as a number.  Numbers in SAS can only be stored one way, as 64 bit binary floating point values.  If you use BEST as an informat then SAS just assumes you meant to use the normal numeric informat, which does not work with strings that contain commas.

Liz_LSU
Calcite | Level 5
SASLooker,

It's not clear from your message where VAR_OLD comes from. Are you reading it in as a character variable? If so, then read it as a numeric variable using the COMMAw.d informat, as Patrick suggested.

In your INPUT statement, the code

VAR_OLD :COMMA5.

will read anything up to four digits, with or without a comma, as a numeric variable. The colon modifier tells SAS to ignore the width (5) and lets you read anything from one- to four-digit numbers. If you don't include the colon modifier and there are numbers less than 1,000, you won't get the expected results for those numbers.

If it's in a permanent SAS data set rather than one you are creating when you run your program, never mind!

liz
deleted_user
Not applicable
thanks for the tip, liz! I am dealing with a permanent SAS data, and for now, everything seems to work out. But I'll bear your suggestion in mind if I deal with other situations.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 11097 views
  • 1 like
  • 6 in conversation