turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Add leading zero based on the number of numbers fo...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-17-2016 10:46 AM

Hello,

I have a situation where I have data like this

```
X_1
X_2
X_3
X_4
X_5
Z_1
Z_2
Z_3
Z_4
Z_5
Z_6
Z_7
Z_8
Z_9
Z_10
Z_11
```

Where the data has a value over 9 I need 1-9 to have a leading zero. Based on the above data I need this to happen

```
X_1
X_2
X_3
X_4
X_5
Z_01
Z_02
Z_03
Z_04
Z_05
Z_06
Z_07
Z_08
Z_09
Z_10
Z_11
```

Here is the code I'm starting to work with. This column of data can have values with no '_' or and '_' with characters after. I only want to do this for values where there are numbers after the '_'.

```
data x ;
set temp ;
start = scan(val, 1, '_') ;
end = scan(val, 2, '_') ;
if end ne '' and ANYALPHA(end) = 0 then output ;
run ;
```

Thank you for any help you can give me.

Accepted Solutions

Solution

06-17-2016
03:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-17-2016 11:41 AM - edited 06-17-2016 11:44 AM

You have to find the W(idth) for each VAL group X and Z. Then you can creat the VAL with the proper number of leading zeros.

```
data val;
input val:$8.;
cards;
X_1
X_4
X_5
Z_1
Z_2
Z_6
Z_7
Z_8
Z_9
Z_10
Z_11
X_2
X_3
Z_3
Z_4
Z_5
Q_1
Q_200
;;;;
run;
data valv / view=valv;
set val;
length valgroup $32;
valgroup = scan(val,1,'_');
valnum = input(scan(val,-1,'_'),8.);
run;
proc sort data=valv out=val2;
by valgroup valnum;
run;
proc print;
run;
proc summary data=val2;
by valgroup;
output out=max(drop=_:) max(valnum)=max;
run;
data val3;
merge val2 max;
by valgroup;
w = floor(log10(max)+1);
length new_val $10;
new_val = catx('_',valgroup,putn(valnum,'Z',w));
run;
proc print;
run;
```

All Replies

Solution

06-17-2016
03:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-17-2016 11:41 AM - edited 06-17-2016 11:44 AM

You have to find the W(idth) for each VAL group X and Z. Then you can creat the VAL with the proper number of leading zeros.

```
data val;
input val:$8.;
cards;
X_1
X_4
X_5
Z_1
Z_2
Z_6
Z_7
Z_8
Z_9
Z_10
Z_11
X_2
X_3
Z_3
Z_4
Z_5
Q_1
Q_200
;;;;
run;
data valv / view=valv;
set val;
length valgroup $32;
valgroup = scan(val,1,'_');
valnum = input(scan(val,-1,'_'),8.);
run;
proc sort data=valv out=val2;
by valgroup valnum;
run;
proc print;
run;
proc summary data=val2;
by valgroup;
output out=max(drop=_:) max(valnum)=max;
run;
data val3;
merge val2 max;
by valgroup;
w = floor(log10(max)+1);
length new_val $10;
new_val = catx('_',valgroup,putn(valnum,'Z',w));
run;
proc print;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-17-2016 11:49 AM

data have;

input x $;

cards;

X_1

X_2

X_3

X_4

X_5

Z_1

Z_2

Z_3

Z_4

Z_5

Z_6

Z_7

Z_8

Z_9

Z_10

Z_11

;

data want;

set have;

x=ifc(index(x,'Z')>0, catx('_',scan(x,1,'_'),put(input(scan(x,2,'_'),2.),z2.)),x);

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-17-2016 11:59 AM

You've received some great options from @data_null__ and @slchen. I don't have a better way, but I'll offer this: I think you should consider using leading 0s for ALL of your values, not just those that exceed a 1-digit length. I think you'll find the consistency is better for sorting and reporting. I don't know the context of your application here, but what if today you have X_1 - X_5, but tomorrow your Xs grow to X_11? The consistency and predictability of the values might help you in the long run.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-17-2016 12:01 PM

Thank you Chris. I'm going through the above solutions now. The reason I can't do that is that I will be joining them based on this value to another table that is coming from someone else and they only add the zero when the count is greater then 9. Thanks again. I'll reply back and accept the solution as soon as I get it resolved. Thank you