topic Re: Dataset summary in Columns in SAS Programming

Dataset summary in Columns

new_sas_user_4 — Thu, 08 Aug 2019 15:16:11 GMT

Hi Everyone ,

I have a dataset in the below format :

data raw_Data;
length brand $4 status $2 ;
input brand $ status $ Units;
datalines;
Audi A 100
Audi NA 200
Audi NA 20
Audi A 400
Audi A 20
Audi A 30
Merc A 77
Merc A 63
Merc A 48
Merc NA 34
Merc A 19
BMW A 4
BMW NA 400
BMW NA 20
BMW NA 30
BMW A 20
;

I want the results as(Sum of units based on status in columns) :

Brand	A	NA
Audi	550	220
Merc	207	34
BMW	24	450

could you please help!!!

Thanks 🙂

Re: Dataset summary in Columns

Astounding — Thu, 08 Aug 2019 15:30:15 GMT

PROC TABULATE would be a good choice here:

proc tabulate data=have;
class brand status;
var units;
tables brand, status=' ' * units=' ' * sum=' ';
run;

Re: Dataset summary in Columns

ballardw — Thu, 08 Aug 2019 15:31:30 GMT

@new_sas_user_4 wrote:

Hi Everyone ,

I have a dataset in the below format :

data raw_Data;
length brand $4 status $2 ;
input brand $ status $ Units;
datalines;
Audi A 100
Audi NA 200
Audi NA 20
Audi A 400
Audi A 20
Audi A 30
Merc A 77
Merc A 63
Merc A 48
Merc NA 34
Merc A 19
BMW A 4
BMW NA 400
BMW NA 20
BMW NA 30
BMW A 20
;

I want the results as(Sum of units based on status in columns) :

Brand A NA

Audi 550 220

Merc 207 34

BMW 24 450

could you please help!!!

Thanks 🙂

Do you want a dataset, used for further computer manipulation, or a report, people read these?

Data set: use proc means or summary with brand and status as class variables and sum the units then transpose the data.

Report could be as simple as

proc report data=work.raw_data;
   column brand status,units ;
   define brand / group;
   define status/ across ' ';
   define units/ analysis ;
run;

proc tabulate data=work.raw_data;
  class brand status;
  var units;
  table brand=' ',
        status=' '*units=' '* sum=' '*f=best8.
        /box='Brand';
run;

Re: Dataset summary in Columns

novinosrin — Thu, 08 Aug 2019 15:35:14 GMT

data raw_Data;
length brand $4 status $2 ;
input brand $ status $ Units;
datalines;
Audi A 100
Audi NA 200
Audi NA 20
Audi A 400
Audi A 20
Audi A 30
Merc A 77
Merc A 63
Merc A 48
Merc NA 34
Merc A 19
BMW A 4
BMW NA 400
BMW NA 20
BMW NA 30
BMW A 20
;

proc sql;
create table want as
select brand,sum((status='A')*units) as A,sum((status='NA')*units) as NA
from raw_Data
group by brand;
quit;

Re: Dataset summary in Columns

new_sas_user_4 — Thu, 08 Aug 2019 17:23:21 GMT

Thank you so much everyone 🙂

All solutions work 🙂

I just have other case statements in my query so I suppose the solution by @novinosrin would be the easiest to implement for me !!

Thank you again!!