BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
starkt964
Calcite | Level 5

@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
Diamond | Level 26

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.

 

 

--
Paige Miller
starkt964
Calcite | Level 5
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.
starkt964
Calcite | Level 5
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
Super User

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
Calcite | Level 5

Yes, this is how it looks like.

 

 

Screen Shot 2022-05-01 at 3.02.52 PM.png

Tom
Super User Tom
Super User

@starkt964 wrote:

Yes, this is how it looks like.

 

 

Screen Shot 2022-05-01 at 3.02.52 PM.png


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
Super User

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
Super User Tom
Super User

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
Calcite | Level 5
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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
starkt964
Calcite | Level 5

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.

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

 

 

Screen Shot 2022-05-01 at 6.41.44 PM.pngScreen Shot 2022-05-01 at 6.50.12 PM.pngScreen Shot 2022-05-01 at 6.50.21 PM.png

Kurt_Bremser
Super User

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
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 30 replies
  • 3043 views
  • 0 likes
  • 6 in conversation