So I guess I am confused by the fact that I cannot convert this variable from character to numeric since all 3 of its observations are numbers.
SAS has no trouble converting "12" to a number, and performing math with it.
col_1="12";
result = 2 * col_1;
What is in the denominators? (BIGCOL1 for example may not contain a valid numeric expression.)
You're right, I can do calculations with col_1, but I checked and, bigcol1,2,3 are all numeric. And I did the same test with bigcol1 and SAS also can perform math with it.
bigcol:
Length: 8
Format: Best12.
Type: Numeric
@kmardinian wrote:
292 data numprop;
293
294 col_1 = '5(25.0)';
295 bigcol1=1000;
296 ratio=col_1/bigcol1;
297 run;
NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
296:7
NOTE: Invalid numeric data, col_1='5(25.0)' , at line 296 column 7.
col_1=5(25.0) bigcol1=1000 ratio=. _ERROR_=1 _N_=1
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 296:12
NOTE: The data set WORK.NUMPROP has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
So it converted col_1 to numeric, but seems to not like the format it's in?
'5(25.0)'
is NOT(!) a number, it is a text with digits in it. SAS can automatically convert text to numbers when the text contains nothing but blanks, digits, maximally one minus or plus sign, and maximally one period.
Which number would you want to get when you convert this text manually? 5? 25? Or something completely different?
@Astounding wrote:
That's unexpected (having no additional notes in the log), and a change in how SAS works.
Here's a way to zero in on the problem, based on the notes you received earlier.
This is an invalid calculation:
col_1 = '5(25.0)';
bigcol1 = 1000;
ratio = col_1 / bigcol1;
There's no way to convert COL_1 to a legitimate number that can be used in a formula.
The PUT() function can use both character and numeric inputs. SAS is "kind" enough to convert the format specification to match the type of the input. So you effectively used the $8. format instead of the F8.0 format, which is why the output matched the input (or at least the first 8 characters of the input.).
Try it with the PUTN() function instead and you should see notes in the log.
Hi Tom, it unfortunately gave me the same thing as before and just printed (%) for col variables, it also still lists col variables as character after I run this.
Should I try and convert the col variables to numeric using PUTN in a separate data step before I run this code
3945 data num_prop ;
3946 retain rowlabel col_1 col_2 col_3 ;
3947 merge num_prop bignumber;
3948 by comnum;
3949 if col_1 ne '0(0.0)' then col_1=cat(strip(putn(col_1, 8.0)),' (', strip(putn((col_1/bigcol1)*100,8.1)) ,'%)');
3950 else col_1=col_1;
3951 if col_2 ne '0(0.0)' then col_2=cat(strip(putn(col_2, 8.0)),' (', strip(putn((col_2/bigcol2)*100,8.1)) ,'%)');
3952 else col_2=col_2;
3953 if col_3 ne '0(0.0)' then col_3=cat(strip(putn(col_3, 8.0)),' (', strip(putn((col_3/bigcol3)*100,8.1)) ,'%)');
3954 else col_3=col_3;
3955 vorder=1;
3956 roworder=2;
3957 run;
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
3949:51 3949:82 3951:51 3951:82 3953:51 3953:82
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
3949:58 3949:101 3951:58 3951:101 3953:58 3953:101
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 3949:87 1 at 3949:96 1 at 3951:87 1 at 3951:96 1 at 3953:87 1 at 3953:96
NOTE: There were 0 observations read from the data set WORK.NUM_PROP.
NOTE: There were 1 observations read from the data set WORK.BIGNUMBER.
NOTE: The data set WORK.NUM_PROP has 1 observations and 13 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
So now I'm a bit confused...should I be trying to convert the col variables to character or numeric??
I can't seem to find anything that will work
We cannot really answer your question as your original program does not make much sense since it is trying to treat COL_1 as it was both a character string (with values with parentheses in it) and an actual number.
Post an example input data set and what output you want and a verbal explanation of what the rules are.
But it sounds like you want to do something like convert a item count (numerator) and total count (denominator) into a string that represents that count and percentage. So you want something like
if total <= 0 then want=' N/A';
else if count = 0 then want = ' 0';
else want = catt(put(count,4.),'(',put(100*count/total,5.1),')');
I just tried to convert col_1 to numeric with this code, but it also didn't work
4716
4717 data num_prop;
4718 numcol1=putn(col_1,8.0);
4719 drop col_1;
4720 rename numcol1=col_1;
4721 run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
4718:20
INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
4718:1 numcol1
NOTE: Variable col_1 is uninitialized.
NOTE: The data set WORK.NUM_PROP has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
I think the underlying issue is, all my conversions are not working. It just stays a character variable
@kmardinian wrote:
No, the log didn't list anything
13404
13405 data numprop;
13406
13407 col_1 = '5(25.0)';
13408
13409 col_1a = put(col_1, 8.0);
13410
13411 run;
NOTE: The data set WORK.NUMPROP has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 secondsI am going to play around with the put statement and see if I can figure it out. Thank you!
When you use the numeric format 8.0 on a character variable, the SAS datastep compiler assumes that you just missed to type the dollar sign, and uses the character format $8. on its own. You can see that by the fact that col_1 has a length of 7, but col_1a has a length of 8.
Since no conversion needs to be done, you get no NOTE in the log.
I often wish that the datastep compiler would be more restrictive and would hit people verbally over the head for such code.
@Astounding wrote:
Did the log really say nothing about using a character string and the 8.0 format?
You may need to experiment with a few variations, such as:
data test2;
col_1 = 'abcdefghijklmnop';
col_1a = put(col_1, 8.0);
run;
data test3;
col_1 = 20;
col_1a = put(col_1, 8.0);
run;
Start off by getting a feel for what the PUT function expects as input, and what it produces as output.
Apparently SAS has changed some behavior about use of the numeric F format such as 8. when using put. Other numeric formats such as BEST8. or comma8. generate the expected
NOTE 484-185: Format $BEST was not found or could not be loaded.
This thread has gone on for so long that it's difficult to see that the issue has been identified a couple of times. I'm just repeating part of what has already been said here (at least twice). Consider this section of a DATA step:
col_1 = '5(25.0)';
bigcol1 = 1000;
ratio = col_1 / bigcol1;
What would you like the ratio to be? Really, SAS doesn't decide. It complains that "5(25.0)" isn't a number and cannot be used to perform math. There may be a different way to perform math with this, but you first have to decide what the calculation for ratio should be.
Thank you, I am trying to work with what everyone is telling me, but it is still not working. I changed the code so that col variables are no longer 0 (0.0) format. And tried to change the variables so that col_1 is numeric and not a character variable. But for some reason, col_1 is not actually being converted to a numeric variable when I check in in the dataset num_prop
data num_prop1;
set num_prop (keep=vname rowlabel _type_ col_1 col_2 col_3 comnum);
numcol1=input(col_1,8.0);
drop col_1;
rename numcol1=col_1;
run;
data num_prop1 ;
length rowlabel $100 col_1 col_2 col_3 col_4 $32 ;
merge num_prop bignumber;
by comnum;
col_1=cat(strip(put(col_1, 8.0)),' (', strip(put((col_1/bigcol1)*100,8.1)) ,'%)');
col_2=cat(strip(put(col_2, 8.0)),' (', strip(put((col_2/bigcol2)*100,8.1)) ,'%)');
col_3=cat(strip(put(col_3, 8.0)),' (', strip(put((col_3/bigcol3)*100,8.1)) ,'%)');
vorder=1;
roworder=2;
run;
In the program you posted, you are merging in the wrong data set.
NUM_PROP1 contains COL_1 as a numeric.
NUM_PROP contains COL_1 as character.
So the MERGE statement should use NUM_PROP1, not NUM_PROP.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.