Hello all,
I've been struggling with this one for a couple days now so I am forced to ask for help (which I usually try not to do without attempting many things myself). I have an excel file containing numeric variables 24 columns across and 365 rows down. The column headers are yvar1, yvar2, yvar3......yvar24.
EX.
yvar1 yvar2 yvar3........ yvar24
9999.99 999.00 888.12 7777.34
So import into SAS. I need to keep 2 implied decimal places and then have every column be the same length. For example I need yvar1 to be 999999 instead of 9999.99 and yvar2 would need to padded with a leading zero to show 099900. Yvar3 would be 088812 and yvar24 would be 777734.
I've used the compress function to remove the decimal point and add the implied decimal places using the following code :
data compression;
set work.usage;
char1=compress(put(yvar1,10.2),'.');
char2=compress(put(yvar1,10.2),'.');
char3=compress(put(yvar1,10.2),'.');
char4=compress(put(yvar1,10.2),'.');
run;
This works well for keeping the 2 implied decimal places and removing the decimal point. However, I cannot get the variables to pad with leading zeros where necessary.
I've tried using the $z format to do it to no avail.
I tried something like:
data helpme;
set compression;
newchar = put (input(char1,10.2),$z8.)
run;
That code somehow removes my implied decimal points. What the heck am I doing wrong? THANK YOU.
To eliminate the decimal place just multiple by 100 and take the INT() part.
To get it to display these numbers with leading zeros attach the appropriate Z format.
You could have it dynamically calculate the length needed to show the largest value.
data have ;
input yvar1-yvar4 ;
cards;
run;
data want ;
set have end=eof;
array x yvar1-yvar4;
do over x;
x=int(x*100);
maxlen=max(maxlen,length(cats(x)));
end;
if eof then call symputx('maxlen',maxlen);
retain maxlen 1;
drop maxlen;
run;
data want;
set want;
format yvar1-yvar4 Z&maxlen..;
run;
proc print; run;
Hi,
Zx. format is a numeric format. So if you have a number 999.99 and you want a zero in front then apply Z as:
data want;
a=999.99;
format a z7.2;
run;
You may also want to put to character:
data want;
a=999.99;
b=put(a,z7.2);
run;
Unfortunately that doesn't seem to work. Using your code to put it as character for yvar1=974620.49 I get the result of 0974620. It drops my implied decimal places that way. I need it to show 097462049. Any other ideas?
I used :
data zeros;
set importedexcelfile;
newchar = put(yvar1,z7.2);
run;
So for that example (and I don't have SAS open now as leaving):
974620.49
do
data want;
a=974620.49;
b=input(compress(put(a,best.),".",""),z9.);
run;
Trying this:
data want;
a=974620.49;
b=input(compress(put(a,best.),".",""),z9.);
run;
gives me the error the informat Z was not found or could not be loaded.
Does this do what you need?
data want;
format b z9.;
a=974620.49;
b=input(compress(put(a,best.),".",""),9.);
run;
proc print data=want;run;
/********************************************/
I went back and played with different best lengths. g is a rounded amount.
data want;
format b d e f g z9.;
a=974620.49;
b=input(compress(put(a,best.),".",""),9.);
d=input(compress(put(a,best10.),".",""),9.);
e=input(compress(put(a,best9.),".",""),9.);
f=input(compress(put(a,best11.),".",""),9.);
g=input(compress(put(a,best8.),".",""),9.);
run;
proc print data=want;run;
/***
Obs b d e f g a
1 000974620 097462049 097462049 009746204 009746205 974620.49
***/
Message was edited by: James Willis
Originally, it appeared that you wanted every numeric to be expressed using 6 characters. In that case, the easy way would be:
newvar = put(oldvar*100, z6.);
If you need longer strings to represent bigger numbers, you'll have to describe how to express some of the larger numbers. How would you express these?
1.12
12.12
123.12
1234.12
12345.12
123456.12
But if you only want 6 characters, the simple statement above should work.
To eliminate the decimal place just multiple by 100 and take the INT() part.
To get it to display these numbers with leading zeros attach the appropriate Z format.
You could have it dynamically calculate the length needed to show the largest value.
data have ;
input yvar1-yvar4 ;
cards;
run;
data want ;
set have end=eof;
array x yvar1-yvar4;
do over x;
x=int(x*100);
maxlen=max(maxlen,length(cats(x)));
end;
if eof then call symputx('maxlen',maxlen);
retain maxlen 1;
drop maxlen;
run;
data want;
set want;
format yvar1-yvar4 Z&maxlen..;
run;
proc print; run;
First off....Thanks to everyone who replied. I appreciate the help and I learn when I read suggestions.
Tom, this is perfect. Does exactly what I need it to. Thank you very much.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.