BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ChrisWoo
Obsidian | Level 7

My input as follow,

BrandModelJan Sales ('mil)Feb Sales ('mil)TrendJan' Car SoldFeb' Car SoldTrend
ToyotaVIOS1015Increase15361985Increase
ToyotaCAMBRY1512decrease16841430decrease
ToyotaYARIS1115Increase13561758Increase
ToyotaAVANZA910Increase11231254Increase

 

I wish to generate the follow output

BrandModelTypeJanFebTrend
ToyotaYARISSales1115Increase
ToyotaYARISCar Sold13561758Increase
ToyotaVIOSSales1015Increase
ToyotaVIOSCar Sold15361985Increase
ToyotaCAMBRYSales1512decrease
ToyotaCAMBRYCar Sold16841430decrease
ToyotaAVANZASales910Increase
ToyotaAVANZACar Sold11231254Increase

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisWoo
Obsidian | Level 7

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;

 

View solution in original post

5 REPLIES 5
Kathryn_SAS
SAS Employee

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
Obsidian | Level 7

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
SAS Employee

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
Diamond | Level 26

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;

 

 

 

--
Paige Miller
ChrisWoo
Obsidian | Level 7
Thanks for your advice.
I will give it a try.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 339 views
  • 2 likes
  • 3 in conversation