topic Re: in proc sql how to differentiate the selected column with condition in SAS Programming

in proc sql how to differentiate the selected column with condition

Kayla_Tan222 — Wed, 29 May 2019 09:00:02 GMT

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

Re: in proc sql how to differentiate the selected column with condition

Oligolas — Wed, 29 May 2019 09:07:16 GMT

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;

Re: in proc sql how to differentiate the selected column with condition

Kayla_Tan222 — Wed, 29 May 2019 09:14:18 GMT

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.

Re: in proc sql how to differentiate the selected column with condition

Oligolas — Wed, 29 May 2019 11:43:23 GMT

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;

Re: in proc sql how to differentiate the selected column with condition

Kayla_Tan222 — Thu, 30 May 2019 00:38:51 GMT

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;