BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DubsNU
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

  1. 9999.99 999.00 888.12 7777.34

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;

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

DubsNU
Calcite | Level 5

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

DubsNU
Calcite | Level 5

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.

jwillis
Quartz | Level 8

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 

Astounding
PROC Star

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.

Tom
Super User Tom
Super User

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;

  1. 9999.99 999.00 888.12 7777.34

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;

DubsNU
Calcite | Level 5

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.

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!

How to Concatenate Values

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.

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
  • 8 replies
  • 7403 views
  • 6 likes
  • 5 in conversation