DATA Step, Macro, Functions and more

Number Format leading Numbers

Reply
Frequent Contributor
Posts: 140

Number Format leading Numbers

Hi, I am importing an excel spreadhseet with around 5000 mortgage numbers. Some numbers are only 7 numbers in length so for those accounts I need to add a leading zero. The code used below, any idea why I get the error message here please?

 

17 mortgage=put(_mortgage,z10.);
____
484
NOTE 484-185: Format $Z was not found or could not be loaded.

 

CODE WRITTEN BELOW 

 

data MIRA_RED_ACCOUNTS_2016;
infile "/opt/sas/data/uk/Personal Folders/Scants/MIRA Red Accounts2016.csv";
input mortgage : $10.;
run;

data mira;
set WORK.MIRA_RED_ACCOUNTS_2016 (rename=(mortgage=_mortgage));
mortgage=put(_mortgage,z10.);
run;

PROC Star
Posts: 7,363

Re: Number Format leading Numbers

The Z. format only works with numeric fields so you first have to convert it. e.g.:

data MIRA_RED_ACCOUNTS_2016;
  input mortgage : $10.;
  cards;
123
4567
;

data mira;
  set WORK.MIRA_RED_ACCOUNTS_2016 (rename=(mortgage=_mortgage));
  mortgage=put(input(_mortgage,10.),z10.);
run;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 5,085

Re: Number Format leading Numbers

You have one dollar sign too many:

 

input mortgage : $10.;

 

Get rid of the dollar sign to create MORTGAGE originally as a numeric variable.  Your final DATA step will convert it to character.

Frequent Contributor
Posts: 140

Re: Number Format leading Numbers

if I remove the $ from input mortgage : $10.; then all I see is a dot for my mortgage?

Super User
Posts: 5,085

Re: Number Format leading Numbers

If you are getting . for the mortgage values, it indicates that there are other characters (non-numeric) present.  To overcome that, leave the $ in place and try:

 

len = length(mortgage);

if len < 10 then mortgage = repeat('0', 9-len) || mortgage;

Contributor
Posts: 62

Re: Number Format leading Numbers

Consider using an ATTRIB statement:

 

data MIRA_RED_ACCOUNTS_2016;
      attrib mortgage format=z10. ;
      input mortgage : 10.;
cards;
123
4567
;
run;

 

I rarely have a data step w/o an attrib. Define the variables for the data step before creating them so your final dataset is exactly as you want.

 

PROC Star
Posts: 7,363

Re: Number Format leading Numbers

Given the problem you are confronting It would help if you attach a representative sample dataset.

 

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 6 replies
  • 153 views
  • 3 likes
  • 4 in conversation