Macro that outputs table with testing results of SAS table

Question

Problem

I'm not a very experienced SAS user, but unfortunately the lab where I can access data is restricted to SAS. Also, I don't currently have access to the data since it is only available in the lab, so I've created simulated data for testing.

I need to create a macro that gets the values and dimensions from a PROC MEANS table and performs some tests that check whether or not the top two values from the data make up 90% of the results.

As an example, assume I have panel data that lists firms revenue, costs, and profits. I've created a table that lists n, sum, mean, median, and std. Now I need to check whether or not the top two firms make up 90% of the results and if so, flag if it's profit, revenue, or costs that makes up 90%.

I'm not sure how to get started

Here are the steps :

1. Read the data

2. Read the PROC MEAN table created, get dimensions, and variables.

3. Get top two firms in each variable and perform check

4. Create new table that lists variable, value from read table, largest and second largest, and flag.

5. Then print table

Simulated data :

https://www.dropbox.com/s/ypmri8s6i8irn8a/dataset.csv?dl=0

PROC MEANS Table

proc import datafile="/folders/myfolders/dataset.csv"
     out=dt
     dbms=csv
     replace;
     getnames=yes;
run;

TITLE "Macro Project Sample";
PROC MEANS n sum mean median std;
    VAR V1 V2 V3;
RUN;

Desired Results :

        Value        Largest     Sec. Largest       Flag
V1     463138.09     9888.09       9847.13     
V2     148.92        1.99           1.99      
V3     11503375      9999900       1000000           Y

Answer 1

At the moment I can't open your simulated dataset but I can give you some advices, hope they will help.

You can add the n extreme values of given variables using the 'output out=' statement with the option IDGROUP.

Here an example using charity dataset ( run this to create it http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#p1oii7oi6k9gfxn19hxiiszb70ms.htm)

proc means data=Charity;
   var MoneyRaised HoursVolunteered;
   output out=try sum=
   IDGROUP ( MAX (Moneyraised HoursVolunteered) OUT[2] (moneyraised hoursvolunteered)=max1 max2);
run;
data    var1 (keep=name1 _freq_ moneyraised max1_1 max1_2 rename=(moneyraised=value max1_1=largest max1_2=seclargest name1=name))
        var2 (keep=name2 _freq_ HoursVolunteered max2_1 max2_2 rename=(HoursVolunteered=value max2_1=largest max2_2=seclargest name2=name));
length name1 name2 $4;
set try ;
name1='VAR1';
name2='VAR2';
run;

data finalmerge;
length flag $1;
set var1 var2;
if largest+seclargest > value*0.9 then flag='Y';
run;

in the proc means I choose to variables moneyraised and hoursvolunteered, you will choose your var1 var2 var3 and make your changes in all the program.

The IDgroup will output the max value for both variables, as you see in the parentheses, but with out[2], obviously largest and second largest.

You must rename them, I choose to rename max1 and max 2, then sas will add an _1 and _2 to the first and the second max values automatically.

All the output will be on the same line, so I do a datastep referencing 2 datasets in output (data var1 var2) keeping the variables needed and renaming them for the next merge, I also choose a naming system as you see.

Finally I'll merge the 2 datasets created and add the flag.

Answer 2

Here are some initial steps and pointers in a non macro approach which restructures the data in such a manner that no array processing is required. This approach should be good for teaching you a bit about manipulating data in SAS but will not be as fast a single pass approach (like the macros you originally posted) as it transposes and sorts the data.

First create some nice looking dummy data.

/* Create some dummy data with three variables to assess */
data have;
    do firm = 1 to 3;
        revenue = rand("uniform");
        costs = rand("uniform");
        profits = rand("uniform");
        output;
    end;
run;

Transpose the data so all the values are in one column (with the variable names in another).

/* Move from wide to deep table */
proc transpose 
        data = have 
        out = trans 
        name = Variable;
    by firm;
    var revenue costs profits;
run;

Sort the data so each variable is in a contiguous group of rows and the highest values are at the end of each Variable group.

/* Sort by Variable and then value 
    so the biggest values are at the end of each Variable group */
proc sort data = trans;
    by Variable COL1;
run;

Because of the structure of this data, you could go down through each observation in turn, creating a running total, which when you get to the final observation in a Variable group would be the Variable total. In this observation you also have the largest value (the second largest was in the previous observation).

At this point you can create a data step that:

• Is aware when it is in the first and last values of each variable group
  • by statement to make the data step aware of your groups
  • first.Variable temporary variable so you can initialise your total variable to 0
  • last.Variable temporary variable so you can output only the last line of each group
• Sums up the values in each group
  • retain statement so SAS doesn't empty your total with each new observation
  • sum() function or + operator to create your total
• Creates and populates new variables for the largest and second largest values in each group
  • lag() function or retain statement to keep the previous value (the second largest)
• Creates your flag
• Outputs your new variables at the end of each group
  • output statement to request an observation be stored
  • keep statement to select which variables you want

---

The macros you posted originally looked like they were meant to perform the analysis you are describing but with some extras (only positive values contributed to the Total, an arbitrary number of values could be included rather than just the top 2, the total was multiplied by another variable k1198, negative values where caught in the second largest, extra flags and values were calculated).