Rearrange the table with SAS

[ChrisWoo]

My input as follow,

Brand	Model	Jan Sales ('mil)	Feb Sales ('mil)	Trend	Jan' Car Sold	Feb' Car Sold	Trend
Toyota	VIOS	10	15	Increase	1536	1985	Increase
Toyota	CAMBRY	15	12	decrease	1684	1430	decrease
Toyota	YARIS	11	15	Increase	1356	1758	Increase
Toyota	AVANZA	9	10	Increase	1123	1254	Increase

I wish to generate the follow output

Brand	Model	Type	Jan	Feb	Trend
Toyota	YARIS	Sales	11	15	Increase
Toyota	YARIS	Car Sold	1356	1758	Increase
Toyota	VIOS	Sales	10	15	Increase
Toyota	VIOS	Car Sold	1536	1985	Increase
Toyota	CAMBRY	Sales	15	12	decrease
Toyota	CAMBRY	Car Sold	1684	1430	decrease
Toyota	AVANZA	Sales	9	10	Increase
Toyota	AVANZA	Car Sold	1123	1254	Increase

[Kathryn_SAS]

You can use PROC TRANSPOSE to do this. It requires multiple steps. Code is below.

/* create sample data */
data one;
brand='Toyota';
input model $ jan_sales feb_sales jan_carsold feb_carsold trend $;
cards;
VIOS 10 15 1536 1985 Increase
CAMBRY 15 12 1684 1430 decrease
YARIS 11 15 1356 175 Increase
AVANZA 9 10 1123 1254 Increase
;
run;

proc sort data=one;
by brand model trend;
run;

proc transpose data=one out=two;
by brand model trend;
var jan_sales feb_sales jan_carsold feb_carsold;
run;

data two;
set two;
varname=scan(_name_,1,'_');
type=scan(_name_,2,'_');
drop _name_;
run;

proc sort data=two;
by brand model trend type;
run;

proc transpose data=two out=three(drop=_name_);
by brand model trend type;
var col1;
id varname;
run;

proc print data=three;
run;

[ChrisWoo · Accepted solution]

Thanks a million to your code.

I got my result.

Btw, may I know how does these work?

proc transpose data=two out=three(drop=_name_);
by brand model trend type;
var col1;
id varname; /* What is this ID statement for? I don't quite get it*/
run;

[Kathryn_SAS]

The ID Statements turns the values into the new column names. I would recommend the SAS Documentation for additional information:

PROC TRANSPOSE: ID Statement 

[PaigeMiller]

Just because you can create this rearrangement of the data does not mean it is a good thing to do. Most (all?) SAS data analysis procedures expect long data sets, and not wide data sets. Having variables named jan and feb makes programming difficult, especially if you have to re-run this program again next month. Having a column such as Jan and Feb which has values that are really different scales in the same column is not a good idea. Having months as text strings is a poor idea as well, since April is the first month of the year if you try to do any sorting.

The following produces a data set named FOUR that is much easier to work with, and if new months are added then the only thing in the code you would have to is in DATA THREE where additional months have to be programmed. (There might be more efficient ways to get to DATA FOUR than the code I now show) Better yet, on the next problem, start with data that is arranged like data set FOUR.

data one;
    brand='Toyota';
    input model $ jan_sales feb_sales jan_carsold feb_carsold trend $;
cards;
VIOS 10 15 1536 1985 Increase
CAMBRY 15 12 1684 1430 decrease
YARIS 11 15 1356 175 Increase
AVANZA 9 10 1123 1254 Increase
;
run;
proc transpose data=one(drop=trend) out=two;
    by brand model notsorted;
    var jan_sales--feb_carsold;
run;

data three;
    set two;
    monthname=scan(_name_,1,'_');
    varname=scan(_name_,2,'_');
    if monthname='jan' then date=mdy(1,1,2023);
    else if monthname='feb' then date=mdy(2,1,2023);
    format date monname3.;
    drop _name_;
run;

proc sort data=three;
    by brand model date;
run;

data four;
    merge three(where=(varname='carsold') rename=(col1=carsold)) three(where=(varname='sales') rename=(col1=sales));
    by brand model;
    prev_carsold=lag(carsold);
    prev_sales=lag(sales);
    if not first.model then do;
        if carsold>prev_carsold then trend_carsold='Increase'; else trend_carsold='Decrease';
        if sales>prev_sales then trend_sales='Increase'; else trend_sales='Decrease';
    end;
    drop varname prev_: monthname;
run;

[ChrisWoo]

Thanks for your advice.
I will give it a try.