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 🙂
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;
PROC TABULATE would be a good choice here:
proc tabulate data=have;
class brand status;
var units;
tables brand, status=' ' * units=' ' * sum=' ';
run;
@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;
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;
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.