I am using this code to do a left join so that I will have a data row for every county in the state, even if there are no data records for that county in my dataset.
proc sql;
create table new as
select frame.*, tmp.count,
coalesce(tmp.count,frame.count) label='count1'
from frame left join tmp
on frame.fipscode=tmp.fipscode ;
quit;
proc print data=new noobs; title2 '2. sql';
run;
Here are the two tables I'm joining:
---------------------------------
1. TMP - numerator dataset
fipscode count
. 115
1 19
5 5
7 2
9 7
13 16
15 12
17 3
25 14
27 3
29 1
35 6
37 1
43 2
45 12
47 1
49 2
51 2
53 2
55 1
61 4
---------------------------------
2. Data Frame
count fipscode
0 1
0 3
0 5
0 6
0 7
0 9
0 11
0 13
0 15
0 17
0 19
0 21
0 23
0 25
0 27
0 28
0 29
0 31
0 33
0 35
0 37
0 39
0 41
0 43
0 45
0 47
0 49
0 51
0 53
0 55
0 57
0 59
0 61
0 98
0 99
0 .
And here is the result of the join
---------------------------------
2. sql
count fipscode _TEMA001
0 . 115
0 1 19
0 3 0
0 5 5
0 6 0
0 7 2
0 9 7
0 11 0
0 13 16
0 15 12
0 17 3
0 19 0
0 21 0
0 23 0
0 25 14
0 27 3
0 28 0
0 29 1
0 31 0
0 33 0
0 35 6
0 37 1
0 39 0
0 41 0
0 43 2
0 45 12
0 47 1
0 49 2
0 51 2
0 53 2
0 55 1
0 57 0
0 59 0
0 61 4
0 98 0
0 99 0
But I'd like to name the variable that results from the coalesce. SAS is naming it _TEMA001.
Can I set the name for the output variable in the sql code? Or do I need to read the dataset in a rename it in a data step?
Thanks!
