Group by procedure

[sasfreaky]

Hi, 

I am trying to group this one variable it also has to summarize the time for an associated variable. How would I go about doing this?

below is the code and the output.  The BUCS code is repeating and i would like to have 1 BUCS code and 1 total CPU time consumed for it.

Thanks

//SYSIN   DD *
 OPTIONS NOCENTER;
 MACRO _BY1
   SYSID
   COSTCTR1
   DATASRC
   %
 MACRO _KEEP1
   SYSID
   COSTCTR1
   LGRQNT
   LGRCPC
   DATASRC
   %
 MACRO _SELECT
   IF LGRQNT > 0;
   IF LGRCPC NE ' ';
   RUN;
   %
 *;
 DATA MTH1 ;
   SET MTH1.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH2 ;
   SET MTH2.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH3 ;
   SET MTH3.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH4 ;
   SET MTH4.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH5 ;
   SET MTH5.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH6 ;
   SET MTH6.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH7 ;
   SET MTH7.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTH9 ;
   SET MTH9.ACTLGR01 ;
   KEEP _KEEP1 ;
   _SELECT;
   RUN;
 DATA MTHS;
   SET MTH1 MTH2 MTH3 MTH4 MTH5 MTH6 MTH7 MTH9  ;
   RUN;
 PROC SORT; BY _BY1 ;
 *;
 PROC SORT NODUPS;
   BY SYSID COSTCTR1 DATASRC;
   RUN;
 PROC PRINT LABEL;
  BY SYSID;
    VAR SYSID COSTCTR1 LGRQNT LGRCPC DATASRC;
    LABEL LGRQNT='CPU TIME';
    LABEL LGRCPC='COMPCODE';
    RUN;

                                                         Data Source
           System      BUCS                                 (Component
  Obs    Identifier    CODE         CPU TIME    COMPCODE    or File ID)

   51       ASYS       TTSM            0.840      0422          SMF
   52       ASYS       TTSP            1.960      0422          SMF
   53       ASYS       TTSP            0.400      0422          SMF
   54       ASYS       TTSP            0.300      0422          SMF
   55       ASYS       TTS2            4.970      0422          SMF
   56       ASYS       TTS2            2.090      0422          SMF
   57       ASYS       TUNI            0.220      0422          SMF
   58       ASYS       TU44           23.700      0422          SMF
   59       ASYS       TU44            0.710      0422          SMF
   60       ASYS       TU44           82.870      0422          SMF

[ChrisNZ]

---

i would like to have 1 BUCS code and 1 total CPU time consumed for it.

No summing is done anywhere, so how do you expect a result summed by BUCS Code?

[sasfreaky]

 Hi Chris,

I am new to SAS.  I am looking for how to get the output to show a single line for each unique BUCS code and the total summarized time.  I don't know how to do this.

Thanks

[ChrisNZ]

Like this? Not the most efficient but much better than what you had.

//SYSIN   DD *
 OPTIONS NOCENTER;
*;
 data MTHS ;
   set MTH1.ACTLGR01 
       MTH2.ACTLGR01
       MTH3.ACTLGR01
       MTH4.ACTLGR01
       MTH5.ACTLGR01
       MTH6.ACTLGR01
       MTH7.ACTLGR01
       MTH8.ACTLGR01
       MTH9.ACTLGR01 ;
   where LGRQNT > 0 and LGRCPC ne ' ';
   keep SYSID
        COSTCTR1
        LGRQNT
        LGRCPC
        DATASRC ;
run;
*;
proc sql; 
  select SYSID, COSTCTR1, sum(LGRQNT) 'CPU TIME', sum(LGRCPC) 'COMPCODE', DATASRC
  from MTHS 
  group by SYSID, COSTCTR1, DATASRC   
  order by SYSID, COSTCTR1, DATASRC ;
quit;

[sasfreaky]

Hi Chris,

Thanks for the reply!  Great improvement on code! Good way for me to learn.

Almost all looks good. One issue I have is the lines are repeated.

System      BUCS                      or File   
Identifier  CODE  CPU TIME  COMPCODE  ID)       
------------------------------------------------
ZSYS        ????   3067211  1203      DB2       
ESYS        ????   3067211  1203      DB2       
FSYS        ????   3067211  1203      DB2       
HSYS        ????   3067211  1203      DB2       
FSYS        ????   3067211  1203      DB2       
ESYS        ????   3067211  1203      DB2       
HSYS        ????   3067211  1203      DB2       
DSYS        ????   3067211  1203      DB2       
HSYS        ????   3067211  1203      DB2       
ZSYS        ????   3067211  1203      DB2       

So instead of all of those lines, I just need 1. Looks like I can add a nodupkey? Is that only doable with a PROC statement?

Thanks!

[sasfreaky]

also, how hard would it be to make a CSV output?

thanks

[ChrisNZ · Accepted solution]

If you need just one line, then you need to remove all the text variables from the SELECT statement, and remove the ORDER BY and GROUP BY clauses. Just keep the sums and you'll obtain the overall sum.

[sasfreaky]

Hi Chris,

Thank you!  I was able to get the list with no repeats:

BUCS          
CODE  CPU TIME
--------------
????   3067211
unxx  2042.451
ABRM  844.4435
AFSC  1030.946
AHTH  45.63628
AHXC  1154.612
AIEP  27.87384
AYNS  4089.737
AYQM  18689.32
AY6U  1.324373
DBMS  77397.87
FCFP  326.0942
FC2A  204.1249
FC32  0.156292
GAGA  276.8414

The code looks like this as you suggested in the SELECT statement.

//SYSIN   DD *                                
 OPTIONS NOCENTER;                            
*;                                            
 DATA MTHS ;                                  
   SET MTH1.ACTLGR01                          
       MTH2.ACTLGR01                          
       MTH3.ACTLGR01                          
       MTH4.ACTLGR01                          
       MTH5.ACTLGR01                          
       MTH6.ACTLGR01                          
       MTH7.ACTLGR01                          
       MTH8.ACTLGR01                          
       MTH9.ACTLGR01                          
       MTH10.ACTLGR01 ;                       
   WHERE LGRQNT > 0 AND LGRCPC EQ '1504';     
   WHERE LGRQNT > 0 AND LGRCPC EQ '1004';     
   WHERE LGRQNT > 0 AND LGRCPC EQ '1203';     
   KEEP SYSID                                 
        COSTCTR1                              
        LGRQNT                                
        LGRCPC                                
        DATASRC ;                             
RUN;                                        
*;                                          
PROC SQL;                                   
  SELECT COSTCTR1, SUM(LGRQNT) 'CPU TIME'   
  FROM MTHS                                 
  GROUP BY COSTCTR1                         
  ORDER BY COSTCTR1 ;                       
QUIT;                                       

Thanks again!

[ChrisNZ]

The last where clause replaces the previous two. Use

 where LGRQNT > 0 and LGRCPC in ( '1504', '1004', '1203' );     

[sasfreaky]

Hi Chris,

Many thanks. I knew there was a way to list of them like that. Blanked on using the single quotes for strings!

Thanks

[sasfreaky]

Hi Chris and community,

I added 2 more variables to the dataset and am able to SELECT them.  When these are added, the report shows repeated lines. How do I group these and have 1 line?

000044    KEEP SYSID                                                     
000045         COSTCTR1                                                  
000046         COSTCTR2                                                  
000047         COSTCTR3                                                  
000048         LGRQNT                                                    
000049         LGRCPC                                                    
000050         DATASRC ;                                                 
000051  RUN;                                                             
000052  *;                                                               
000053  PROC SQL;                                                        
000054    SELECT COSTCTR1, COSTCTR2, COSTCTR3, SUM(LGRQNT) 'CPU TIME'    
000055    FROM MTHS                                                      
000056    GROUP BY COSTCTR1                                              
000057    ORDER BY COSTCTR1 ;                                            
000058  QUIT;                                                            

What it looks like: The CPU time is correct, but I want to only have 1 of those lines. 

BUCS                                                                          
CODE  APPL ID1                        APPL ID2                        CPU TIME
------------------------------------------------------------------------------
BATC                                                                  132206.3
BATC                                                                  132206.3
BATC                                                                  132206.3
BATC                                                                  132206.3

There will be values in APPLID 1 and 2.

This is what I want but with APPL ID 1 and 2 added:

BUCS          
CODE  CPU TIME
--------------
BATC  132206.3
CGPA   22.9771
CHCA  2329.231
CHPA  325.7014
DBMS  11202.84
DB2C  22682.53
EBDA  14407.88
FC2A  0.127076

I tried added another group by line, add to the existing group by, but didnt get anywhere.  Remove duplicates?

Thanks,

[sasfreaky]

also saw you posted:
If you need just one line, then you need to remove all the text variables from the SELECT statement, and remove the ORDER BY and GROUP BY clauses. Just keep the sums and you'll obtain the overall sum.

the APPLID 1 and 2 have is a mix of numbers and letters so I cant do the above?