Dataset summary in Columns

[new_sas_user_4]

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 🙂 

[Astounding]

PROC TABULATE would be a good choice here:

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

[ballardw]

---

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

[novinosrin · Accepted solution]

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;

[new_sas_user_4]

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!!