topic Re: Group by to summarize values in SAS Programming

Group by to summarize values

Kiteulf — Mon, 05 Sep 2022 11:45:04 GMT

I have data like :

Nbr	Performance	Country	Prod_Group	PerformanceL12M
11746164	Missed	FIJ	Text	4
11746164	Performed	FIJ	Text	8
11715062	Performed	FIJ	Text	1
10922641	Missed	FIJ	Text	2
11178781	Missed	FIJ	Text	1
11800620	Missed	FIJ	Text	12
11739987	Missed	FIJ	Text	12
12300588	Missed	FIJ	Text	12
2711647	Missed	FIJ	Text	2
2711647	Performed	FIJ	Text	1

I want it to be summarized like this:

Country	Prod_Group	Nevermissed	MissedOnce	MissedTwice	MissedThrice	MissedMore
FIJ	Text	1	1	2		4
EQK	Text	etc	etc	etc	etc	etc

Re: Group by to summarize values

PeterClemmensen — Mon, 05 Sep 2022 08:51:06 GMT

I don't fully understand this. How are MissedOnce, MissedTwice etc. calculated?

Why is MissedOnce = 1 in the result below?

Re: Group by to summarize values

Kiteulf — Mon, 05 Sep 2022 08:56:31 GMT

Nbr= 11715062, has no Missed obs, only Performed, hence that obs has never missed.

Nbr = 11178781 has Missed =1 , hence that obs would count as MissedOnce

Re: Group by to summarize values

PaigeMiller — Mon, 05 Sep 2022 10:16:37 GMT

@Kiteulf wrote:

Nbr= 11715062, has no Missed obs, only Performed, hence that obs has never missed.

Nbr = 11178781 has Missed =1 , hence that obs would count as MissedOnce

What about 11800620? Why doesn't that count as Missed Once?

How is missedTwice=2 and missedMore=4? I think a more detailed description of the logic used here is very necessary to help us understand what you are doing.

Re: Group by to summarize values

Kiteulf — Mon, 05 Sep 2022 10:22:54 GMT

If I do something like this:

Proc sql;
Create table 			DNK_REPAYMENTPERFORMANCE_L12M  as
select 					t.snp_dt 
						,t.Product
						,t.Country
						,t.Prod_Group
						,t.Totalcount
						,t1.Missrep


from
( 	 
    select snp_dt, Product, Country,Prod_Group, count(Contractnbr) as TotalCount
    from DNK_REPAYMENTPERFORMANCE_L12M_a 
    group by snp_dt, Product, Country,Prod_Group)t
and
( 
    select snp_dt, Product, Country,Prod_Group,RepaymentPerformanceL12M, count(Contractnbr) as MissRep
    from DNK_REPAYMENTPERFORMANCE_L12M_a
	where Repayment_performance='Repayment missed'
    group by snp_dt, Product, Country,Prod_Group,RepaymentPerformanceL12M)t1 
 
;
Quit;

It says:

and
___
22
76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

How can I have two subtables created in one query?

Re: Group by to summarize values

PaigeMiller — Mon, 05 Sep 2022 10:25:29 GMT

Please show us the entire log for this PROC SQL, not selected parts. Paste the log into the window that appears when you click on the </> icon.

Re: Group by to summarize values

Kiteulf — Mon, 05 Sep 2022 10:27:20 GMT

1                                                          The SAS System                            08:49 Monday, September 5, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='DNK COSTOFFUNDS';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='\\colosas001\SAS_NORWAY\Risk\B2C Secured\Playground\Sverre\Colosas SAS prosjekter\CC 0.13.egp';
6          %LET _CLIENTPROJECTPATHHOST='ZLE02763';
7          %LET _CLIENTPROJECTNAME='CC 0.13.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         


28         Proc sql;
29         Create table 			DNK_REPAYMENTPERFORMANCE_L12M  as
30         select 					t.snp_dt
31         						,t.Product
32         						,t.Country
33         						,t.Prod_Group
34         						,t.Totalcount
35         						,t1.Missrep
36         
37         
38         from
39         ( 	
40             select snp_dt, Product, Country,Prod_Group, count(Contractnbr) as TotalCount
41             from DNK_REPAYMENTPERFORMANCE_L12M_a
42             group by snp_dt, Product, Country,Prod_Group)t
43         and
           ___
           22
           76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.  

ERROR 76-322: Syntax error, statement will be ignored.

44         (
45             select snp_dt, Product, Country,Prod_Group,RepaymentPerformanceL12M, count(Contractnbr) as MissRep
46             from DNK_REPAYMENTPERFORMANCE_L12M_a
47         	where Repayment_performance='Repayment missed'
48             group by snp_dt, Product, Country,Prod_Group,RepaymentPerformanceL12M)t1
2                                                          The SAS System                            08:49 Monday, September 5, 2022

49         /*on*/
50         /*	t.snp_dt=t1.snp_dt and t.Product=t1.Product and t.Country=t1.Country and t.Prod_Group=t1.Prod_Group)a*/
51         
52         ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
53         Quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              436.21k
      OS Memory           33120.00k
      Timestamp           09/05/2022 12:19:55 PM
      Step Count                        95  Switch Count  0
      
54         
55         %LET _CLIENTTASKLABEL=;
56         %LET _CLIENTPROCESSFLOWNAME=;
57         %LET _CLIENTPROJECTPATH=;
58         %LET _CLIENTPROJECTPATHHOST=;
59         %LET _CLIENTPROJECTNAME=;
60         %LET _SASPROGRAMFILE=;
61         %LET _SASPROGRAMFILEHOST=;
62         
63         ;*';*";*/;quit;run;
64         ODS _ALL_ CLOSE;
65         
66         
67         QUIT; RUN;
68

Re: Group by to summarize values

PaigeMiller — Mon, 05 Sep 2022 10:28:48 GMT

You can't use and in the FROM clause in PROC SQL.

Re: Group by to summarize values

Kiteulf — Mon, 05 Sep 2022 10:31:38 GMT

Yes I understand,

But what can I use to do to add another subsquery?

Re: Group by to summarize values

PaigeMiller — Mon, 05 Sep 2022 10:35:56 GMT

First, before I can advise further, you have to provide answers to my earlier questions, where I don't understand your logic and I don't understand your output.

Re: Group by to summarize values

Kiteulf — Mon, 05 Sep 2022 11:30:43 GMT

Thanks!

Re: Group by to summarize values

Ksharp — Mon, 05 Sep 2022 11:41:30 GMT

data have;
infile cards expandtabs truncover;
input Nbr Performance :$20. Country $ Prod_Group $ PerformanceL12M;
cards;
  11746164 Missed FIJ Text 4
  11746164 Performed FIJ Text 8
  11715062 Performed FIJ Text 1
  10922641 Missed FIJ Text 2
  11178781 Missed FIJ Text 1
  11800620 Missed FIJ Text 12
  11739987 Missed FIJ Text 12
  12300588 Missed FIJ Text 12
  2711647 Missed FIJ Text 2
  2711647 Performed FIJ Text 1
;

proc sql;
create table temp as
select Country,Prod_Group,Nbr,sum(PerformanceL12M) as n_miss
from 
(
select Country,Prod_Group,Nbr,PerformanceL12M from have where Performance='Missed'
union all
select Country,Prod_Group,Nbr,0 from have where Performance ne 'Missed'
)
   group by Country,Prod_Group,Nbr 
;


create table want as
select Country,Prod_Group,
 sum(n_miss=0) as Nevermissed,
 sum(n_miss=1) as MissedOnce,
 sum(n_miss=2) as MissedTwice,
 sum(n_miss=3) as MissedThrice,
 sum(n_miss>3) as MissedMore
 from temp 
  group by Country,Prod_Group
;
quit;