Help using Base SAS procedures

Naming the output variable from proc sql coalesce

Reply
Occasional Contributor
Posts: 7

Naming the output variable from proc sql coalesce

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!

Super User
Posts: 19,878

Re: Naming the output variable from proc sql coalesce

Posted in reply to LoisHNewMexico

Rename using AS NEW_NAME


E.G.

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

Occasional Contributor
Posts: 7

Re: Naming the output variable from proc sql coalesce

Thank you!

Ask a Question
Discussion stats
  • 2 replies
  • 448 views
  • 0 likes
  • 2 in conversation