How to group up to 4 columns in proc sql

[Kayla_Tan222]

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.

[Kurt_Bremser]

Please supply example data in a data step with datalines, so we have something to work with.

[Kayla_Tan222]

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.

[koyelghosh]

Is it because of a GROUP BY clause you are having? It is first grouped by class and the output also looks like it is conforming to the command.

[Kurt_Bremser]

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.