BookmarkSubscribeRSS Feed
Kayla_Tan222
Calcite | Level 5

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.

4 REPLIES 4
Kayla_Tan222
Calcite | Level 5

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
Lapis Lazuli | Level 10
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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 877 views
  • 0 likes
  • 3 in conversation