topic Re: Summarize variables on PROC SQL UNION? in SAS Programming

Summarize variables on PROC SQL UNION?

G_I_Jeff — Mon, 09 Aug 2021 14:24:54 GMT

Need to concatenate duplicate tables but also need to summarize variable:

proc sql;                                                    
 create table user.nfctape as                                
  select ident,                                              
         system,                                             
         account,                                            
         start_date,                                         
         end_date,                                           
         sum(ddsusize) as size format 20.5,                  
         hlq                                                 
  from user.plex_init union corresponding                    
  select ident,                                              
         system,                                             
         account,                                            
         start_date,                                         
         end_date,                                           
         sum(ddsusize) as size format 20.5,                  
         hlq                                                 
  from user.test_init                                        
  group by ident, system, account, start_date, end_date, hlq;

Is this even possible in one statement? This code runs but the summarization from first table doesn't take account the GROUP BY statement.

Re: Summarize variables on PROC SQL UNION?

HarrySnart — Mon, 09 Aug 2021 15:52:52 GMT

Hi @G_I_Jeff

You're trying to union two queries, so I think both should have the group by. Also, if I understand your question, if you are concatenating duplicate records and want to retain those you should look at UNION ALL.

I've tried to re-create your code using the cars dataset twice - it adds all duplicates and the sum works with the respective GROUP BY statements.

*Create Dataset1;
data cars1;
set sashelp.cars;
run;

*Create Dataset2;
data cars2;
set sashelp.cars;
run;

*Union both tables including duplicates and summation;
proc sql;                                                    
 create table work.test_union as                                
  (select Make,	Type,	Origin,	DriveTrain,	sum(MSRP)                                                 
  from work.cars1 group by make,type,origin,drivetrain)
 union all                     
  (select Make,	Type,	Origin,	DriveTrain,	sum(MSRP)                                             
  from work.cars2                                        
  group by make,type,origin,drivetrain);
quit;

Does this answer your question?

Thanks

Harry

Re: Summarize variables on PROC SQL UNION?

Patrick — Mon, 09 Aug 2021 16:24:42 GMT

@G_I_Jeff Below two options with different result. Want1 first concatenates (union corr) the tables and then aggregates, Want2 first aggregates and then concatenates (union corr) the table. The usual case is Want1 but I've added Want2 to showcase what the logic does @HarrySnart proposed.

/*Create sample data*/
data have1;
  do key=1,2,3;
    var=key;
    output;
  end;
  stop;
run;
data have2;
  do key=2,3,4;
    var=key;
    output;
    output;
  end;
  stop;
run;

/*Union both tables including duplicates and summation*/
proc sql;
  create table want1 as    
    select key, sum(var) as sum_var
    from
      ( 
        select key, var                                                 
        from work.have1 
        union all                     
        select key, var                                           
        from work.have2   
      ) 
    group by key
    ;
quit;

/*Union both tables including duplicates and summation*/
proc sql;
  create table want2 as  
    ( 
      select key, sum(var) as sum_var                                                 
      from work.have1 
      group by key
    )
    union all    
    ( 
      select key, sum(var) as sum_var                                           
      from work.have2   
      group by key
    )
    ;
quit;

Re: Summarize variables on PROC SQL UNION?

G_I_Jeff — Mon, 09 Aug 2021 16:26:17 GMT

Thanks Harry. This got me moving forward. The union all was what I needed along with getting the group by clause on the first select.