Finding what percentage of targets are achieved using SAS arrays

[mdhtrivedi]

The data set "Sales.csv" has details of number of units sold across regions of four products.The target sales across regions(North, East, West), of four products() is given as (9450,9100,8550,9700). With the help of SAS arrays I want to find, what was the percentage of targets achieved for each product-region pair.

 Please find an attached file of "Sales.csv" with this post.

I haven't worked around this problem as am clueless for the approach. Some insights or workaround this is appreciated.

Thank you in advance!

[Reeza]

Why arrays? Simple IF/THEN would work just as well.

Write the code with IF/THEN statements first. Once you find the points of repetition you can figure out where to introduce the array to solve it.

[mdhtrivedi]

Reeza,

My concern is how to do summation of each column of the dataset that would then be compared to the target values using IF/THEN statement. I thought it can be done using SAS arrays. Correct me if I am wrong.

[Reeza]

---

@mdhtrivedi wrote:

Reeza,

My concern is how to do summation of each column of the dataset that would then be compared to the target values using IF/THEN statement. I thought it can be done using SAS arrays. Correct me if I am wrong.

---

There's always more than one way to solve a problem. Mine would be to use IF/THEN to create flags and use PROC MEANS to summarize the flags. This would easily allow for multiple levels of aggregation on demand. 

Arrays in SAS are just variables shortcut references and work on a single row at a time. Is that what you're expecting to use?

[ChrisNZ]

If you need to summarise columns in a data step, the RETAIN statement is usually the preferred method.

Otherwise, procedures summarise very well as pointed out.

Try it and then see if you need to ask further question.

[ballardw]

---

@mdhtrivedi wrote:

The data set "Sales.csv" has details of number of units sold across regions of four products.The target sales across regions(North, East, West), of four products() is given as (9450,9100,8550,9700). With the help of SAS arrays I want to find, what was the percentage of targets achieved for each product-region pair.

 Please find an attached file of "Sales.csv" with this post.

I haven't worked around this problem as am clueless for the approach. Some insights or workaround this is appreciated.

Thank you in advance!

---

This is the second time you have asked this question without providing what the target goals are. There is not really any way to answer this without knowing what the goals are or how the data with the goals is organized.

Do you have a SAS data set to work from yet?

[mdhtrivedi]

Mr Ballardw, 

This is the second time I am mentioning you, I have attached the sample dataset file named "Sales.csv" with the original post in initial itself. What kind of dataset would you prefer to have, an excel file or an image?

[Reeza]

He wants a data set you can copy and run as a data step, pasted into the forum, not as an attachment. Human nature, the more steps you make us take to help you, the less likely you're going to get help. Vague questions get vague answers and as soon you require a specific type of solution (arrays) it's likely homework, which means I'm less inclined to help without the poster showing some significant effort (that's a personal rule). Not a criticism or anything, just some feedback on how your posts are likely being received. 

Instructions on how to create a data step are found here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---

@mdhtrivedi wrote:

Mr Ballardw, 

This is the second time I am mentioning you, I have attached the sample dataset file named "Sales.csv" with the original post in initial itself. What kind of dataset would you prefer to have, an excel file or an image?

---

[mdhtrivedi]

Okay. I will improve my details and explanation more descriptive and clear.
Thanks for honest feedback.

[ballardw]

---

@mdhtrivedi wrote:

Mr Ballardw, 

This is the second time I am mentioning you, I have attached the sample dataset file named "Sales.csv" with the original post in initial itself. What kind of dataset would you prefer to have, an excel file or an image?

---

Your example data is a matrix of season and values with column headings of Product_1, which from the tab name indicates these are some sort of sales number. If those are "targets" then there is no sales data to compare with the targets.

Region Product_1 Product_2 Product_3 Product_4
North   8600     9300      8500      11000
East    9500     9500      8100      9800
West    9260     8900      9200      9300

If those are sales values then there is not a "target value" provided.

You ask in the title and the question: "percentage of targets achieved". We do not know 1) what any target value is so we cannot 2) determine which if any target was "achieved" whatever that might be, assumption likely is that the product sale/region combination is equal to or larger than a target value. 3) We don't know if the "target" is a number of units sold or currency value, 4) we don't know if the values above are units sold or currency value .

To calculate a "percentage of targets achieved" you need to know 1) the number of targets achieved and 2) the total number of targets, which above implies there are 12. Then you would calculate percentage by dividing the number of targets achieved by the total number of targets.

So you have only provided half the data for the question provided.

If I am supposed to figure out if the North region Product_1 sales of 8600 achieved its target, what value is 8600 compared to for determining if it was met? You need a complete additional matrix of the target values to answer the question. Or if the target was supposed to be exactly the same for each region/product combination (unlikely but possible for a school exercise) then we need that value.

[Reeza]

@ballardw It is stated in the original post. IF/THEN statements are simpler here though IMO.

The target sales across regions(North, East, West), of four products() is given as (9450,9100,8550,9700)

[ballardw]

---

@Reeza wrote:

@ballardw It is stated in the original post. IF/THEN statements are simpler here though IMO.

The target sales across regions(North, East, West), of four products() is given as (9450,9100,8550,9700)

---

So I missed that.  I find it poorly stated.

[mdhtrivedi]

Hereby I explain my problem clearly. Below is the dataset given:-

Region Product_1 Product_2 Product_3 Product_4 North 8600 9300 8500 11000 East 9500 9500 8100 9800 West 9260 8900 9200 9300

 I am importing the data file by following code snippet:-

PROC IMPORT OUT=Sales
DATAFILE= "/home/folder/Sales.csv"
DBMS=CSV REPLACE ;
RUN;

Now I am doing summation of each products sales across different regions by following code:-

Proc Means Data = Sales Sum;
Var Product_1 - Product_4;
Run;

I am given targets for each product and now I have got sum of each products sales, that looks like below:-

Region	Product_1	Product_2	Product_3	Product_4
North	8600	9300	8500	11000
East	9500	9500	8100	9800
West	9260	8900	9200	9300
Sum	27360	27700	25800	30100
Target	9450	9100	8550	9700

Now my intention is to calculate percentage of targets achieved of each product across all regions whose calculation looks like below:-

Region	Product_1	Product_2	Product_3	Product_4
North	8600	9300	8500	11000
East	9500	9500	8100	9800
West	9260	8900	9200	9300
Sum	27360	27700	25800	30100
Target	9450	9100	8550	9700
Percentage of targets achieved	(9450*100)/27360	(9100*100)/27700	(8550*100)/25800	(9700*100)/30100

Now I want to achieve the calculation of percentage of targets achieved for each product across the regions in sas.

I could do untill summation but further I want some insights further into this. 

Please feel free to ask if there is any query. 

[Reeza]

Are you sure that percentage formula is correct? This is why it's so important to post the data. I clearly interpreted this problem significantly differently.

From my solution - look at proc means to summarize the data. And the data step can be modified to calculate the percentage afterwards, similarly. Or you can access the targets at least. Given what I've posted, you should have enough to make an attempt and if you're having issues, post your full code include the data step, like I did at the top of my post.

[Reeza · Accepted solution]

Since you're new, we'll take it easy but please do try and implement changes when you post a new question. 

Here's how I'd do it.

*create sample data;
data have;
infile cards dlm=',' dsd;
input Region $ Product_1-Product_4;
cards;
North,8600,9300,8500,11000
East,9500,9500,8100,9800
West,9260,8900,9200,9300
North,8600,4500,8500,23000
East,9500,9500,8100,9800
West,9260,2200,9200,400
North,8600,1300,8500,5000
East,9500,1200,8100,9800
West,9260,8900,9200,200
;;;;
run;

*transpose to a long format and create some flags for some targets;
data long;
set have;

array prod(4) product_1-product_4;
array targets (4) _temporary_ (9450, 9100, 8550, 9700);
flag=0;
do Product=1 to dim(prod);
    Value = prod(product);
    flag = value > targets(product);
    output;
end;

run;

*output;
proc means data=long n mean stackods;
class region product;
types region product region*product;
var flag;
ods output summary=answer;
run;

title 'Target reached for Product, Region and Product*Region';
proc print data=answer;
    format mean percent12.1;
run;

*if you want a displayed report;
proc tabulate data=long;
class product region;
var flag;
table (region all)*(product all), flag*mean*f=percent12.1;
run;