Hi, I need to group the 4 columns but it keep showing the grouping that I don't want.
I expect my result will be like this below
class tema002 accyr_fy accmth_fy zclmpd
1 0 2018 12 2600
1 1 2018 12 81945.95
1 2 2018 12 300
2 0 2018 12 5515
2 1 2018 12 88451
2 2 2018 12 7463
.....
8 1 2018 12 7455
8 2 2018 12 8451
1 0 2019 1 544
1 1 2019 1 8465
1 2 2019 1 2310
2 0 2019 1 48410
2 1 2019 1 98651
2 2 2019 1 1253
.....
8 1 2019 1 942
8 2 2019 1 47121
However, if I type my code like this,
PROC SQL;
create table SMCD_PD_&yymm. AS
SELECT put(zmajrsk,$class.) as class, min(accyr_fy-lossyr_fy,11),accyr_fy,accmth_fy,sum(zclmpd) AS zclmpd
FROM pl&yymm..POLA_clm_agg_&yymm.
WHERE (accyr_fy*100+accmth_fy)<=201901 & (accyr_fy*100+accmth_fy)>=201812
GROUP BY class,2,3,4;
run;
ods html
file="\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\result\SMCD_mth_PD_FY_10DY_&yymm..html"
style=normal;
proc print;
The result is something like this,
class tema002 accyr_fy accmth_fy zclmpd
1 0 2018 12 2600
1 0 2019 1 544
1 1 2018 12 81945.95
1 1 2019 1 8465
1 2 2018 12 300
1 2 2019 1 2310
2 0 2018 12 5515
2 0 2019 1 48410
2 1 2018 12 88451
2 1 2019 1 98651
2 2 2018 12 7463
2 2 2019 1 1253
.....
.....
8 1 2018 12 7455
8 1 2019 1 942
8 2 2018 12 8451
8 2 2019 1 47121
I have no idea how to group it into the way I want, I tried and error many times but still don't get it.
Please supply example data in a data step with datalines, so we have something to work with.
This is my complete code
%let startyr=2018;
%let startmth=12;
%let endyr=2019;
%let endmth=1;
%let yymm=%eval(&endyr*100+&endmth);
libname pl&yymm. "\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\DBF";
Data pl&yymm..POLA_clm_agg_&yymm.;
set pl&yymm..POLA_clm_agg_&yymm.;
run;
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_&yymm. AS
SELECT put(zmajrsk,$class.) as class, min(accyr_fy-lossyr_fy,11),accyr_fy,accmth_fy,sum(zclmpd) AS zclmpd
FROM pl&yymm..POLA_clm_agg_&yymm.
WHERE (accyr_fy*100+accmth_fy)<=201901 & (accyr_fy*100+accmth_fy)>=201812
GROUP BY class,2,3,4;
run;
ods html
file="\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\result\SMCD_mth_PD_FY_10DY_&yymm..html"
style=normal;
proc print;
run;
I did not do any calculation, I just import the sas file and then get the grouping result.
Please post an example for pl&yymm..POLA_clm_agg_&yymm. in a data step with datalines, so we can test how the code interacts with the data.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.