PROC SQL PERCENT OF SALES BY ACCOUNT AND STATE

[MJGriggs]

I have 9 data sources joined; some of them are at Account/State level and some are only at Account level. I need to be able to calculate the percentage of sales by Account/State in the base data set so that I can apply that percentage to other Account-only level variables to approximate an Account/State value for numerators and denominators. 

The attached Excel file shows how the formula would work, but I need to translate this to PROC SQL. 

Anyone have any ideas? Any help is greatly appreciated. 

[ballardw]

Why Proc SQL specifically?

When you specify a specific approach then you quite often reduce the flexibility of answers. Also SQL can be quite inefficient for some types of calculations.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[MJGriggs]

SAS PROC SQL is the only tool I have to work with.

[Reeza]

Why would SQL be the only tool?

[Reeza]

PROC FREQ and look if you need the column percent or row percent depending on your table statement.

[MJGriggs]

I don't have any SAS code to share because I haven't figured this out  - but hopefully the screenshot will provide enough context of what I'm attempting to do. 

[ballardw]

Percentage calculations require a clear definition of numerator and denominator.

Pictures don't tell us that.

[MJGriggs]

The numerator is the first occurrence of Data, the denominator is the sum of all of the Data records that match the same Account and State.

[MJGriggs]

So in Column H, all of the records for one account will add up to 100%. Those individual percentages are then used to take Other Data to a newly created State level.

[PaigeMiller]

We can't work with screen captures, we can't work with Excel files. As stated by @ballardw please share your data in the form of working SAS data step code. He gave a link to create such code from your data.

Also, I agree with the others, the tool to use is PROC FREQ and not PROC SQL.

[Reeza]

data have;
informat Customer $8. Variable1 $12.  Variable2 $8.  Variable3 $8.  State $2.  Data comma.  PCT_BY_ACCT_STATE percent12.1 Other_Data1 comma. Other_Data2 comma. Revised_Data1 comma.  Revised_Data2 comma.;
format Customer $8. Variable1 $12.  Variable2 $8.  Variable3 $8.  State $2.  Data comma.  PCT_BY_ACCT_STATE percent12.1 Other_Data1 comma32. Other_Data2 comma32. Revised_Data1 comma32.  Revised_Data2 comma32.;

input Customer  Variable1   Variable2   Variable3   State   Data    PCT_BY_ACCT_STATE Other_Data1 Other_Data2 Revised_Data1   Revised_Data2;
order = _n_;
cards;
47671 Widgets Orange  Cat1    FL   68,867     16.6%    18,580,732      23,783,337      3,088,566   3,953,365 
47671 Widgets Green   Cat2    GA   55,610     13.4%    18,580,732      23,783,337      2,494,013   3,192,336 
47671 Widgets Red Cat3    IL   85,030     20.5%    18,580,732      23,783,337      3,813,449   4,881,215 
47671 Widgets Purple  Cat4    IN   69,993     16.9%    18,580,732      23,783,337      3,139,066   4,018,004 
47671 Widgets Yellow  Cat1    KS   81,193     19.6%    18,580,732      23,783,337      3,641,366   4,660,949 
47671 Widgets Blue    Cat2    MA   53,609     12.9%    18,580,732      23,783,337      2,404,271   3,077,467 
44478 Planes  Orange  Cat3    MD   95,440     16.0%    4,918,157   10,328,130      784,825     1,648,132 
44478 Planes  Green   Cat4    MI   75,786     12.7%    4,918,157   10,328,130      623,206     1,308,732 
44478 Planes  Red Cat1    MO   84,209     14.1%    4,918,157   10,328,130      692,470     1,454,187 
44478 Planes  Purple  Cat2    NC   98,930     16.5%    4,918,157   10,328,130      813,524     1,708,400 
44478 Planes  Yellow  Cat3    NJ   96,327     16.1%    4,918,157   10,328,130      792,119     1,663,450 
44478 Planes  Blue    Cat4    NV   88,203     14.7%    4,918,157   10,328,130      725,313     1,523,158 
44478 Planes  Orange  Cat1    NY   59,186     9.9%     4,918,157   10,328,130      486,700     1,022,070 
13691 Trains  Green   Cat2    OR   81,433     14.9%    11,350,690      12,712,773      1,687,530   1,890,033 
13691 Trains  Red Cat3    PA   65,383     11.9%    11,350,690      12,712,773      1,354,927   1,517,518 
13691 Trains  Purple  Cat4    FL   98,722     18.0%    11,350,690      12,712,773      2,045,808   2,291,305 
13691 Trains  Yellow  Cat1    GA   99,254     18.1%    11,350,690      12,712,773      2,056,833   2,303,653 
13691 Trains  Blue    Cat2    IL   59,038     10.8%    11,350,690      12,712,773      1,223,440   1,370,253 
13691 Trains  Orange  Cat3    IN   86,052     15.7%    11,350,690      12,712,773      1,783,249   1,997,239 
13691 Trains  Green   Cat4    KS   57,854     10.6%    11,350,690      12,712,773      1,198,904   1,342,772 
67520 Auto    Red Cat1    KY   52,717     8.5%     18,417,781      46,044,453      1,558,215   3,895,538 
67520 Auto    Purple  Cat2    LA   72,136     11.6%    18,417,781      46,044,453      2,132,204   5,330,511 
67520 Auto    Yellow  Cat3    MA   94,969     15.2%    18,417,781      46,044,453      2,807,105   7,017,762 
67520 Auto    Blue    Cat4    MD   87,706     14.1%    18,417,781      46,044,453      2,592,424   6,481,061 
67520 Auto    Orange  Cat1    ME   50,499     8.1%     18,417,781      46,044,453      1,492,655   3,731,638 
67520 Auto    Green   Cat2    MI   68,474     11.0%    18,417,781      46,044,453      2,023,963   5,059,906 
67520 Auto    Red Cat3    MN   99,460     16.0%    18,417,781      46,044,453      2,939,850   7,349,626 
67520 Auto    Purple  Cat4    MO   97,143     15.6%    18,417,781      46,044,453      2,871,364   7,178,410 
;
run;


proc sql;
create table want as
select t1.*, data/sum(t1.data) as pct_want format=percent12.1
from have as t1
group by customer
order by t1.order;
quit;