Naming the output variable from proc sql coalesce

LoisHNewMexico — Tue, 03 Feb 2015 23:49:58 GMT

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!

Re: Naming the output variable from proc sql coalesce

Reeza — Tue, 03 Feb 2015 23:52:52 GMT

Rename using AS NEW_NAME

E.G.

coalesce(tmp.count,frame.count) AS COUNT1 label='count1'

Re: Naming the output variable from proc sql coalesce

LoisHNewMexico — Wed, 04 Feb 2015 00:58:42 GMT

Thank you!

topic Naming the output variable from proc sql coalesce in SAS Procedures

Naming the output variable from proc sql coalesce

Re: Naming the output variable from proc sql coalesce

Re: Naming the output variable from proc sql coalesce