BookmarkSubscribeRSS Feed
Sir_Highbury
Quartz | Level 8

Dear experts,

 

here what I have:

data have;
source="y_2016"; segment="a"; revenue=100; output;
source="y_2016"; segment="b"; revenue=50; output;
source="y_2015"; segment="a"; revenue=90; output;
source="y_2015"; segment="b"; revenue=30; output;
run;

 and what I want:

 

data want;
source="y_2016"; segment="a"; revenue=100; output;
source="y_2016"; segment="b"; revenue=50; output;
source="y_2015"; segment="a"; revenue=90; output;
source="y_2015"; segment="b"; revenue=30; output;
source="delta"; segment="a"; revenue=10; output;
source="delta"; segment="b"; revenue=20; output;
source="delta%"; segment="a"; revenue=10/90; output;
source="delta%"; segment="b"; revenue=20/30; output;
run;

 

how can I specify the calculation I want?

5 REPLIES 5
Astounding
PROC Star

You would need to sort the data, to get those results (but in a different order).  For example:

 

proc sort data=have;

by segment source;

run;

 

data want;

set have;

by segment;

if first.segment that starting_value=revenue;

retain starting_value;

output;

if last.segment and (first.segment=0);

source='delta';

revenue = revenue - starting_value;

output;

source='delta%';

revenue = revenue/starting_value;

output;

run;

Sir_Highbury
Quartz | Level 8

Dear @Astounding,

 

thanks a lot, it fits to the case I provided my my case is a bit more complicated: I do not have only revenues but several variables and I cannot list the calculation for all of them. Anny way to automatize it? Thanks again, SH.

Astounding
PROC Star

At this point, there's too much that I would need to guess.  You'll need to show what the inputs and outputs look like when you have an additional variable.

Sir_Highbury
Quartz | Level 8

well let's assume that the input is the following one with revenue from revenue1 to revenue100.

 

data have;
source="y_2016"; segment="a"; revenue1=100; revenue2=70; output;
source="y_2016"; segment="b"; revenue1=50; revenue2=60; output;
source="y_2015"; segment="a"; revenue1=90; revenue2=70; output;
source="y_2015"; segment="b"; revenue1=30; revenue2=700; output;
run;

 

The output should be done in the same way, repeating the calculation for each variable. Basically I am trying to calculate difference between two tables from different years (2015 and 2016) aggregated with the same granularity (all the variables by segment).

Astounding
PROC Star

For that sort of problem, arrays are the typical tool that lets you handle many variables.  Here would be an approach:

 

proc sort data=have;

by segment source;

run;

 

data want;

set have;

by segment;

array revs {100} revenue1-revenue100;

array starts {100} starting_value1-starting_value100;

if first.segment then do _n_=1 to 100;

   starts{_n_} = revs{_n_};

end;

retain starting_value1 - starting_value100;

output;

if last.segment and (first.segment=0);

source='delta';

do _n_=1 to 100;

   revs{_n_} = revs{_n_} - starts{_n_};

end;

output;

source='delta%';

do _n_=1 to 100;

   revs{_n_} = revs{_n_} / starts{_n_};

end;

output;

run;

 

As you add variables, there can be silly things that happen with the data that you might want to account for.  For example, can your % be negative instead of positive?  Can the denominator be zero?  Can there be missing values in your data?  In any case, arrays let you process many variables in the same fashion without adding too much to the code.  The only burdensome part might be adding all the names of the variables to the ARRAY statement.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1165 views
  • 0 likes
  • 2 in conversation