Help using Base SAS procedures

Add leading zero based on the number of numbers for that value

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

Add leading zero based on the number of numbers for that value

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
Respected Advisor
Posts: 3,777

Re: Add leading zero based on the number of numbers for that value

[ Edited ]

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;

 

Capture.PNG 

View solution in original post


All Replies
Solution
‎06-17-2016 03:19 PM
Respected Advisor
Posts: 3,777

Re: Add leading zero based on the number of numbers for that value

[ Edited ]

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;

 

Capture.PNG 

Super Contributor
Posts: 275

Re: Add leading zero based on the number of numbers for that value

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;

Community Manager
Posts: 2,758

Re: Add leading zero based on the number of numbers for that value

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.

Super Contributor
Posts: 398

Re: Add leading zero based on the number of numbers for that value

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
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 290 views
  • 4 likes
  • 4 in conversation