in proc sql how to differentiate the selected column with condition

[Kayla_Tan222]

Hi,

I have used proc sql to select out the variables that I want from my table,

for example, my table is this:

 zmajrsk      chdrnum     dev10yr_fy     zoscv    zclmpd

VEH                A                    2                   Q              5     

VEH                F                     0                   T             10

FIR                  E                   10                   E            9

FIR                 W                    9                  J              45

TEL                 R                    1                   E             82

ENG                W                   0                  R             5

VEH              A                     1                     T              8

FIR                  R                    9                   Y            1

TEL                T                      2                    R           1

BON                T                     1                     E           5

 GMS              R                     1                      T           1

HUL                T                     1                       R         2

my code now is below which is select the dev10yr_fy and sum(zclmpd) variable

proc sql;

select dev10yr_fy,sum(zclmpd) as zclmpd

from polaclm.pola_clm_mth_201812 where accyr_fy=2019 group by 1;

run;

this is the result I get with the code

dev10yr_fy zclmpd

0	23844.39
1	1272905
2	315772.5
3	27831.46
4	45512.54
5	2452.49
6	6.6
7	120
8	96
9	0
10	90

however I want to show a further detail table, by differentiate the zclmpd with different zmajrsk type (VEH,FIR,BON...) without showing the zmajrsk in the table. For ex:

 zmajrsk (without THIS IN THE TABLE)                                                         dev10yr_fy   zclmpd

VEH                                                                                                                     0                       5     

VEH                                                                                                                      1                     82

VEH                                                                                                                      2                    10

FIR                                                                                                                       9                     46  

FIR                                                                                                                     10                       9

TEL                                                                                                                      1                      82

TEL                                                                                                                      2                     1

ENG                                                                                                                      0                     5

BON+GMS+HUL (I want this three to be categorized under same type also)         1                    8 (the sum)

i have no idea what code should i use in the proc sql.

my code in VFOXPRO will be like this,

sele iif(prcl="FIR","VEH","TEL") as cls,dev10yr_fy,sum(zclmpd) as zclmpd from pola_clm_mth_201812 where accyr_fy=2019 grou by 1,2

but i dunno how can i transfer this language to sas

[Oligolas]

Add zmarjrsk to your grouping Statement.

Make sure you use quit for PROC SQL;

PROC SQL;
SELECT dev10yr_fy,sum(zclmpd) AS zclmpd
FROM polaclm.pola_clm_mth_201812
WHERE accyr_fy=2019
GROUP BY zmajrsk,1
;
QUIT;

[Kayla_Tan222]

Hi Oligolas,

First of all ,thanks for your reply.

your method was help.

I have another problem is that I need some of the types in zmajrsk will be group in one type,

For Ex, the data in the variable column zmajrsk become one type

BON+GMS+HUL=OTHERS

so that the table will show the results of OTHERS instead of BON, GMS, HUL each.

[Oligolas · Accepted solution]

I would solve this with a Format I guess

libname polaclm "c:\";
data polaclm.pola_clm_mth_201812;
zmajrsk='VEH';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='VEH';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='VEH';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='FIR';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='FIR';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='TEL';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='TEL';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='BON';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='GMS';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
zmajrsk='HUL';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;
run;

PROC FORMAT;
   value $ oth
   'BON'='OTHERS'
   'GMS'='OTHERS'
   'HUL'='OTHERS'
   ;
RUN;

PROC SQL;
*show the formatted zmajrsk values;
SELECT put(zmajrsk,$oth.) as new_zmajrsk, dev10yr_fy,sum(zclmpd) AS zclmpd
FROM polaclm.pola_clm_mth_201812
WHERE accyr_fy=2019
GROUP BY CALCULATED new_zmajrsk,2
;
*Summarise by formatted values;
SELECT dev10yr_fy,sum(zclmpd) AS zclmpd
FROM polaclm.pola_clm_mth_201812
WHERE accyr_fy=2019
GROUP BY put(zmajrsk,$oth.),1
;
QUIT;

[Kayla_Tan222]

Hi Oligolas,

I do not understand why you put 0 for dec10yr_fy and 5 for zclmpd for this line below, can you explain this to me?

zmajrsk='VEH';dev10yr_fy=0;zclmpd =5;accyr_fy=2019;output;