Addition of macro variables.

[starkt964]

@PaigeMiller 

Please find the dataset below.

data WORK.chrrt;
  infile datalines dsd truncover;
  input Id:BEST. Name:$28. Points:$2.;
  format Id BEST.;
  label Id="Id" Name="Name" Points="Points";
datalines4;
1,Accounts Receivable,4
2,Cash Equivalents,20
3,Current Asset,7
4,Long Term Receivables,24
5,Other Long Term Investments,25
6,Other Asset,9
7,Cash,1
8,Bank,2
9,Credit Card Receivable,3
10,Marketable Securities,21
11,Allowance for Bad Debts,22
12,Other Receivables,5
13,Other Current Asset,23
14,Inventory,6
15,Capital Asset,8
16,Accum. Amort. & Depreciation,26
17,Asset,0
18,Other Non-Current Asset,27
19,Accounts Payable,4
20,Liability,0
21,Cash,1
22,Bank,2
23,Current Liability,7
24,Credit Card Payable,3
25,Other Current Liability,25
26,Other Non-Current Liability,28
27,Debt,6
28,Deferred Income Taxes,29
29,Other Liability,9
30,Other Payable,5
31,Short Term Debt,24
32,Income Tax Payable,23
33,Payroll Tax Payable,21
34,Employee Deductions Payable,22
35,Sales Tax Payable,20
36,Deferred Revenue,27
37,Long Term Debt,26
38,Long Term Liability,8
39,Equity,0
40,Cash,1
41,Bank,2
42,Credit Card Payable,3
43,Owner/Partner Contributions,4
44,Owner/Partner Witdrawals,5
45,Dividends,20
46,Share Capital,6
47,Retained Earnings,7
48,Current Earnings,8
49,Revenue,0
50,Farming Revenue,22
51,Other Revenue,1
52,Gain,23
53,Extraordinary Gain,24
54,Operating Revenue,20
55,Non-Operating Revenue,21
56,Non-Operating Expense,26
57,Extraordinary Loss,28
58,Cost of Goods Sold,1
59,Operating Expense,20
60,Payroll Expense,2
61,Employee Benefits,23
62,Expense,0
63,General & Admin. Expense,3
64,Bad Debt Expense,22
65,Interest Expense,24
66,Amort./Depreciation Expense,21
67,Income Tax Expense,25
68,Loss,27
69,Rent,0
70,Capital and Interest,0
;;;;

[PaigeMiller]

Thank you.

Why do you confuse us with "revenue" and "debt", when these are not a part of your data set?

The output you want, the addition of ID 1 2 and 3, is obtained via the PROC SUMMARY code I presented earlier. Again, no macros needed.

[starkt964]

No, the addition of 1, 2 and 3 needs to be saved in the variable "revenue" likewise with "debt" and needs to be stored in another file.

[Kurt_Bremser]

And which IDs need to be summed for "debt"?

[starkt964]

Id's for debt = 11 + 27 + 31 + 37 + 64. that is 100.
same goes for the taxes, the taxes get summed up the same way.

[Kurt_Bremser]

I guess you have a resource where those mappings are stored; please show us how that looks.

---

@starkt964 wrote:
Id's for debt = 11 + 27 + 31 + 37 + 64. that is 100.
same goes for the taxes, the taxes get summed up the same way.

---

[starkt964]

Yes, this is how it looks like.

[Tom]

---

@starkt964 wrote:

Yes, this is how it looks like.

---

What does that MEAN?   

What do the numbers mean?  Are they the actual dollar amounts for DEBT?  Or are they the ID numbers for the observations number or ID numbers in the dataset that actually has the dollar amounts?

If the former then they are not very useful for a PROGRAM.  Just paste them into Excel and be done with it.

If you really want to convert that string 49 + 54 + 50 into a number then use a simple data step and the RESOLVE() function.   So if we assume the table in your photograph is named HAVE and column with the string is name EQUATION then you could run this data step to create a variable name TOTAL that has the result of evaluating those equations.

data want;
  set have ;
  total = input(resolve(cats('%sysevalf(',EQUATION,')')),32.);
run;

If the later then it would be better to store them as observations and text strings.

data categories;
   infile cards dsd truncover ;
   input category :$30.  id ;
cards;
Revenue,49
Revenue,54
Revenue,50
Expenses,62 
;
data amounts;
  infile cards dsd truncover ;
  input id name :$30. amount ;
cards;
49,Sales,1000
50,Interest Income,30
54,Capital Gains,50
62,Loans,300
;

Now you can combine the two and get a dataset with the categories and the line item labels so you can produce a report.

proc print data=have;
   by category;
   id category;
   var name amount;
   sum amount;
run;

[Kurt_Bremser]

Split this into observations, and you can use it as a CNTLIN dataset for PROC FORMAT. Use the format for name in PROC SUMMARY, and you're done.

[Tom]

So your POINTS variable needs to be numeric, not character, if you want to SUM it

Your current NAME variable has a few values that repeat.  Should the points for those names be collapsed?  Or should the names be made distinct in some way?

You need another variable that describes the category (for example DEBT or REVENUE ) for each of the NAME values.

[starkt964]

Inshort, the "debt" sums up the entire category having debt same with the "revenue" and same with "taxes" and "liquidity" etc. like a hierarchy.

[mkeintz]

OK, you're approaching an adequate description of the problem, which I believe is this.

1. For a set of 70 account categories, you have an account ID, account description, and a numeric value
2. You apparently want to selectively sum these categories into larger groups.  So far you've mentioned DEBT, REVENUE, TAXES.  I presume there are others.
3. It's not clear whether any of the 70 items would be a component of more than one group.

What you haven't provided is a "map" between the seventy categories and the larger groups - with the exception of DEBT.

To get the totals you want, you need a way to provide that map to SAS (per @Kurt_Bremser 's request).   So, in what form do you have that map?  It would be nice if it were in table form (as opposed to

Id's for debt = 11 + 27 + 31 + 37 + 64. that is 100.

BTW, what does "that is 100" mean?

Until you can provide that map in some systematic form, you won't get the help you are asking for.   But by this point, we know it does not need macro coding.

[starkt964]

1. Yes, that is correct.

2.Yes, there are other classified groups as well. (Please find the full mapping attached).

3.Yes, it could be a component of more than one group.

For, Id's for debt = 11 + 27 + 31 + 37 + 64. that is 100.

It means that id(11) = 22, id(27) = 6 , id (31) = 24, id (37) = 26, id(64) = 22 . Summing all of the points makes it 100.

At the end a new file stores the data of the components with the summation value.

Like Total Revenue, 42.

        Interests, 24.

---------------------------------

[Kurt_Bremser]

What do these curly brackets mean?

PLEASE: be clear and complete with your descriptions, leave nothing out, don't force us to pry information out of your nose little bit by little bit.

And POST EXAMPLE DATA NOT(!!!) AS PICTURES, but as DATA STEP CODE!!

[Kurt_Bremser · Accepted solution]

This is code that creates a format out of your ratio components table, and uses this format in the SUMMARY procedure to sum up per group. Both the format and the procedure will use multiple labels.

data ratio;
infile datalines dlm=",";
input name :$100. charter_account_groups :$100.;
datalines;
Total Revenue,49 + 54 + 50
Cash Equivalent,2
Total current assets (short term),{7} + {14} + 9 + 14 + 3 + 2
;

data have;
infile datalines dlm=",";
input id name :$100. points;
datalines;
2,Cash Equivalents,20
3,Current Asset,7
7,Cash,1
9,Credit Card Receivable,3
14,Inventory,6
49,Revenue,0
50,Farming Revenue,22
54,Operating Revenue,20
;

data cntlin;
retain fmtname "ratio";
set ratio (rename=(name=label)) end=done;
length hlo $2;
hlo = "M";
do i = 1 to countw(charter_account_groups,"+");
  start = compress(scan(charter_account_groups,i,"+")," ","kd");
  output;
end;
if done
then do;
  hlo = "MO";
  start = "other";
  label = "other";
  output;
end;
drop i charter_account_groups;
run;

proc format cntlin=cntlin;
run;

proc summary data=have nway;
format id ratio.;
class id / mlf;
var points;
output out=want (drop=_type_ _freq_ rename=(id=name)) sum()=;
run;