topic Re: Can i put legend/label in my proc sql table? in SAS Programming

Can i put legend/label in my proc sql table?

Kayla_Tan222 — Thu, 30 May 2019 06:48:52 GMT

Hi all,

I am wondering if it possible to put legend for my table created using proc sql

here is my code

PROC SQL;

create table SMCD_PD_FY_10DY_201812 AS

SELECT put(zmajrsk,$class.) as class, dev10yr_fy,sum(zclmpd) AS zclmpd

FROM polaclm.pola_clm_agg_201812

WHERE accyr_fy=2019

GROUP BY class,2;

I have attach a screenshot of my proc sql table

I want o label the class beside my table, For Ex:

1=VEH

2=FIR

3=LIA

4=ENG

can I put a legend statement inside the code? or my idea is wrong?

Re: Can i put legend/label in my proc sql table?

Patrick — Thu, 30 May 2019 06:54:04 GMT

@Kayla_Tan222

You can create a format (Proc Format) and then assign this format permanently to the variable when you create it in the SQL Select Clause.

put(zmajrsk,$class.) as class format=myformat.

You can also define a label for your variable like:

put(zmajrsk,$class.) as class label='1=VEH, 2=FIR, 3=LIA, 4=ENG'

Re: Can i put legend/label in my proc sql table?

PeterClemmensen — Thu, 30 May 2019 06:54:25 GMT

I think what you want to do is this. Roll out your own numeric format for the class variable and assign it in the select clause of PROC SQL.

proc format lib=work;
   value class 1="VEH"
               2="FIR"
               3="LIA"
               4="ENG";
run;

proc sql;
   create table smcd_pd_fy_10dy_201812 as
      select zmajrsk as class format=class., 
             dev10yr_fy,
             sum(zclmpd) as zclmpd
      from polaclm.pola_clm_agg_201812
      where accyr_fy=2019
      group by class,2;
quit;

Re: Can i put legend/label in my proc sql table?

Kayla_Tan222 — Thu, 30 May 2019 08:12:36 GMT

Hi draycut, my full code is actually this. I put the number for VEH,FIR,...,ENG because I want them to be ordered when shown in the table. However, I want to show the VEH,FIR...,..ENG in my table as well

PROC FORMAT;

value $ class

'VEH'='1'

'FIR'='2'

'GMS'='3'

'BON'='3'

'HUL'='3'

'PAC'='4'

'TEL'='5'

'WWC'='5'

'LIA'='6'

'CGO'='7'

'ENG'='8'

;

RUN;

PROC SQL;

create table SMCD_PD_FY_10DY_201812 AS

SELECT put(zmajrsk,$class.) as class, dev10yr_fy,sum(zclmpd) AS zclmpd

FROM polaclm.pola_clm_agg_201812

WHERE accyr_fy=2019

GROUP BY class,2

;

Re: Can i put legend/label in my proc sql table?

Patrick — Thu, 30 May 2019 08:34:31 GMT

@Kayla_Tan222

A SAS Format is for printing but doesn't change the internal value. So if you just apply the format then your data will still be ordered by the internal values but if you look at the data then you'll see the formatted values.