How to group up to 4 columns in proc sql

Kayla_Tan222 — Fri, 28 Jun 2019 07:14:08 GMT

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.

Re: How to group up to 4 columns in proc sql

Kurt_Bremser — Fri, 28 Jun 2019 07:03:52 GMT

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

Re: How to group up to 4 columns in proc sql

Kayla_Tan222 — Fri, 28 Jun 2019 07:16:20 GMT

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.

Re: How to group up to 4 columns in proc sql

koyelghosh — Fri, 28 Jun 2019 07:30:04 GMT

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.

Re: How to group up to 4 columns in proc sql

Kurt_Bremser — Fri, 28 Jun 2019 09:30:25 GMT

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.

topic How to group up to 4 columns in proc sql in Graphics Programming

How to group up to 4 columns in proc sql

Re: How to group up to 4 columns in proc sql

Re: How to group up to 4 columns in proc sql

Re: How to group up to 4 columns in proc sql

Re: How to group up to 4 columns in proc sql