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!
Rename using AS NEW_NAME
E.G.
coalesce(tmp.count,frame.count) AS COUNT1 label='count1'
Thank you!
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.