DATA Step, Macro, Functions and more

Adding Zeros at a specific location of the variable

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Adding Zeros at a specific location of the variable

Hi,

I have a database with Id's with strings of characters of different lengths whereas they must be normally identical. here is the example: 

Id

CA000015432204

CA008552136654

CA8552136654

CA08552136654

CA000000300205

CA300205

The third and the fourth id are normally the same as the second, but with a lack of typing there are some missing zeros after the "CA" code. Similarly for the fifth and the sixth Id.

What I look for is How I can add as many zeros right after the "CA" code so that I will have
a total number of characters equal to 14 as for the first, second, and fifth id, and the  result will look like this :

Id

CA000015432204

CA008552136654

CA008552136654

CA008552136654

CA000000300205

CA000000300205

My data contain more than a million of observations and many country codes lake "CA" for "Canada".

I think substr and compress can do the trick but I find no way to do it.

Thank you very much.

 

 


Accepted Solutions
Solution
‎10-04-2017 09:44 AM
Respected Advisor
Posts: 4,779

Re: Adding Zeros at a specific location of the variable

[ Edited ]

@soumri

Under the assumption that the digits start at position 3 below code should do.

data sample;
  infile datalines truncover;
  input have_str:$14.;
  length want_str $14;
  want_str=substrn(have_str,1,2)||put(input(substr(have_str,3),f16.),z12.);
  datalines;
CA000015432204
CA008552136654
CA8552136654
CA08552136654
CA000000300205
CA300205
;
run;

If you need to standardize you data on the data base side then use pass-through SQL. I'm pretty sure such standardization can also be done with database functions.

 

5 Oct 2017: Replaced PUTN() with PUT() in code above.

View solution in original post


All Replies
Solution
‎10-04-2017 09:44 AM
Respected Advisor
Posts: 4,779

Re: Adding Zeros at a specific location of the variable

[ Edited ]

@soumri

Under the assumption that the digits start at position 3 below code should do.

data sample;
  infile datalines truncover;
  input have_str:$14.;
  length want_str $14;
  want_str=substrn(have_str,1,2)||put(input(substr(have_str,3),f16.),z12.);
  datalines;
CA000015432204
CA008552136654
CA8552136654
CA08552136654
CA000000300205
CA300205
;
run;

If you need to standardize you data on the data base side then use pass-through SQL. I'm pretty sure such standardization can also be done with database functions.

 

5 Oct 2017: Replaced PUTN() with PUT() in code above.

PROC Star
Posts: 394

Re: Adding Zeros at a specific location of the variable

Nice solution, I did'nt get it to work in EG (sas 9.2), so I changed the putn:

data sample;

infile datalines truncover;

input have_str $14.;

length want_str $14;

want_str=substrn(have_str,1,2)||put(input(substr(have_str,3),f16.),z12.);

datalines;

CA000015432204

CA008552136654

CA8552136654

CA08552136654

CA000000300205

CA300205

;

run;

 

//Fredrik

Contributor
Posts: 65

Re: Adding Zeros at a specific location of the variable

@FredrikE @Patrick

Thank you  both of you, very good solution,
Thank you FredriKE for the rectification you have made to  let the Patrick's code work with my very old version of SAS. I'm sorry, I'll accept Patrick's solution for the SAS community to benefit from the latest updates. Thank you again for both of you.

Contributor
Posts: 65

Re: Adding Zeros at a specific location of the variable

@FredrikE @Patrick

Why you put the f16. I can not understand the meaning.

Respected Advisor
Posts: 4,779

Re: Adding Zeros at a specific location of the variable

[ Edited ]

@soumri

Fw.d is just one of the available SAS formats you can use to write a number as a string of digits. With your data other SAS formats would return the same result, i.t. 16. or BEST16.

 

As for PUTN() vs. PUT: PUT() and PUTN() will return the same result in your case. Using PUT() is actually better here (performs better). PUTN() allows you to change a format during data step execution which can be very useful - but that's nothing we need to do here. 

 

Using PUTN() was a "typo". I'm going to change the code I've posted.

Contributor
Posts: 65

Re: Adding Zeros at a specific location of the variable

Thank you @Patrickfor the very satisfactory clarifications. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 181 views
  • 6 likes
  • 3 in conversation