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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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