BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Kiteulf
Quartz | Level 8

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

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

 

Why is MissedOnce = 1 in the result below?

Kiteulf
Quartz | Level 8

 

 

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

PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
Kiteulf
Quartz | Level 8

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?

PaigeMiller
Diamond | Level 26

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.

 

 

--
Paige Miller
Kiteulf
Quartz | Level 8
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         
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Kiteulf
Quartz | Level 8

 

Yes I understand,

 

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Kiteulf
Quartz | Level 8

Thanks!

Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 11 replies
  • 805 views
  • 0 likes
  • 4 in conversation