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
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
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;
- Cheers -
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;
- Cheers -
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.
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;
- Cheers -
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.