Hi guys,
suppose to have the following table:
data have;
input Hosp1 Hosp2 ;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;;;;
run;
Is there a way to get the following table? In other words I need to add a point after the first 3 numbers from the beginning of the integer while leaving the integers with <= 3 numbers as they are. Thank you very much in advance!
Hosp1 | Hosp2 |
123.4 | 112 |
345.678 | 562.3 |
212 | 323.45 |
567.62 | 131.2 |
400.2 | 762.3451 |
780.98 | 771.2 |
121.2 | 900.087 |
560.9 | 243.2 |
Sure.
The first step is to clearly state the RULE (or rules) that define the changes you want to make.
So you have this data.
data have;
input Hosp1 Hosp2 ;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;
But you expected this data:
data expect;
input Hosp1 Hosp2 ;
cards;
123.4 112
345.678 562.3
212 323.45
567.62 131.2
400.2 762.3451
780.98 771.2
121.2 900.087
560.9 243.2
;
Looks to me like you wanted the decimal point after the third digit.
For that you can use the BZ informat.
data want;
set have;
hosp1=input(put(hosp1,7.-L),bz7.4);
hosp2=input(put(hosp2,7.-L),bz7.4);
run;
Result
The COMPARE Procedure Comparison of WORK.WANT with WORK.EXPECT (Method=EXACT) Data Set Summary Dataset Created Modified NVar NObs WORK.WANT 05MAR24:11:04:23 05MAR24:11:04:23 2 8 WORK.EXPECT 05MAR24:11:04:23 05MAR24:11:04:23 2 8 Variables Summary Number of Variables in Common: 2. Observation Summary Observation Base Compare First Obs 1 1 Last Obs 8 8 Number of Observations in Common: 8. Total Number of Observations Read from WORK.WANT: 8. Total Number of Observations Read from WORK.EXPECT: 8. Number of Observations with Some Compared Variables Unequal: 0. Number of Observations with All Compared Variables Equal: 8. NOTE: No unequal values were found. All values compared are exactly equal.
But the real question is why did you end up with the wrong value in the HAVE dataset?
How did you create the HAVE dataset? Perhaps you can modify that step so that it is created with the right values directly.
For example:
data have2;
input Hosp1 bz7.4 Hosp2 bz7.4 ;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;
Sure.
The first step is to clearly state the RULE (or rules) that define the changes you want to make.
So you have this data.
data have;
input Hosp1 Hosp2 ;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;
But you expected this data:
data expect;
input Hosp1 Hosp2 ;
cards;
123.4 112
345.678 562.3
212 323.45
567.62 131.2
400.2 762.3451
780.98 771.2
121.2 900.087
560.9 243.2
;
Looks to me like you wanted the decimal point after the third digit.
For that you can use the BZ informat.
data want;
set have;
hosp1=input(put(hosp1,7.-L),bz7.4);
hosp2=input(put(hosp2,7.-L),bz7.4);
run;
Result
The COMPARE Procedure Comparison of WORK.WANT with WORK.EXPECT (Method=EXACT) Data Set Summary Dataset Created Modified NVar NObs WORK.WANT 05MAR24:11:04:23 05MAR24:11:04:23 2 8 WORK.EXPECT 05MAR24:11:04:23 05MAR24:11:04:23 2 8 Variables Summary Number of Variables in Common: 2. Observation Summary Observation Base Compare First Obs 1 1 Last Obs 8 8 Number of Observations in Common: 8. Total Number of Observations Read from WORK.WANT: 8. Total Number of Observations Read from WORK.EXPECT: 8. Number of Observations with Some Compared Variables Unequal: 0. Number of Observations with All Compared Variables Equal: 8. NOTE: No unequal values were found. All values compared are exactly equal.
But the real question is why did you end up with the wrong value in the HAVE dataset?
How did you create the HAVE dataset? Perhaps you can modify that step so that it is created with the right values directly.
For example:
data have2;
input Hosp1 bz7.4 Hosp2 bz7.4 ;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;
This is a very concise and elegant solution. Very nice, @Tom 😎
One more approach. "Format-less".
data have;
input Hosp1 Hosp2 ;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;
run;
data want;
set have;
array H hosp1 hosp2;
do over H;
H = H/(10**max(0,ceil(log10(H))-3)); /* divide by 10,100,1000, etc. depending on digits number */
end;
format hosp1 hosp2 best.;
run;
proc print;
run;
Bart
@NewUsrStat wrote:
Thank you very much Tom! Unfortunately I received data in the "have" format.
Then you probably need to ask the supplier why they did that.
Note that if any of the values could start with zero then you are in trouble figuring out where to up the decimal place. For example consider the string:
1234
Is that supposed to be 123.4 or was it instead 012.34 or 001.234 ?
You are probably going to be better off storing these a CHARACTER variables instead of NUMERIC variables. First off a CODE does not need to be a NUMBER because you cannot do arithmetic with it. Second you can then have meaningful leading and/or trailing zeros.
You could either convert the original numbers you have.
chosp1 = put(hosp1,7.-L);
Or if you define CHOSP1 as length $8 you could add the period in a second step.
length chosp1 $8;
chosp1 = put(hosp1,7.-L);
chosp1 = substr(chosp1,1,3)||'.'||substr(chosp1,4);
Read as character then use a different Input for different lengths to imply a decimal value
data have; input Hosp1 $ Hosp2 $ ; select (length(hosp1)); when (1,2,3) Hosp1Num = input(hosp1,3.); when (4) Hosp1Num = input(hosp1,4.1); when (5) Hosp1Num = input(hosp1,5.2); when (6) Hosp1Num = input(hosp1,6.3); when (7) Hosp1Num = input(hosp1,7.4); otherwise put "WARNING: Unexpected length for Hosp1"; end; select (length(hosp2)); when (1,2,3) Hosp2Num = input(Hosp2,3.); when (4) Hosp2Num = input(Hosp2,4.1); when (5) Hosp2Num = input(Hosp2,5.2); when (6) Hosp2Num = input(Hosp2,6.3); when (7) Hosp2Num = input(Hosp2,7.4); otherwise put "WARNING: Unexpected length for Hosp2"; end; cards; 1234 112 345678 5623 212 32345 56762 1312 4002 7623451 78098 7712 1212 900087 5609 2432 ;
Probably just one way.
When there are 4 or more digits (i.e. value >=1000), you want to convert the value such that exactly three digits precede the decimal point.
Assuming there is good reason to maintain these variables as numerics instead of character, then:
data have (drop=i);
input hosp1 hosp2;
array hosp {2} ;
do i=1 to 2;
do while (hosp{i}>=1000);
hosp{i}=hosp{i}/10;
end;
end;
cards;
1234 112
345678 5623
212 32345
56762 1312
4002 7623451
78098 7712
1212 900087
5609 2432
;;;;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.