I have about 175 csv files of the same format as attached to this message. With each file I want to be able to read it into SAS and construct the following values and then save the data to a new outputted csv file.
1. Normalised earnings per share = row 124/row 5 * 100
2. Dividend payment per share per fiscal year (depends on the fiscal year of the firm, dates are shown in the file and for the attached file it is June 30 of each year ). Calculate the dividend payment = (interim dividend per share + the final dividend per share + any other dividend payments made between 1 July 19X0 and 30 June 19X1) That is the dividend per fiscal year spans two calendar year dates. See rows 167 and 171 for the interim and final dividends per share and rows 169 and 173 for the dates on which the dividend payments are made respectively.
3. Dividend payout _NORM = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/Normalised earnings per share (from (1) above)
4. Dividend payout_ADJ = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/adjusted earnings per share (row 170 on file)
5. ROA = EBIT/TA = Row 104/Row 47
6. ROA2 = EBITDA/TA = Row 101/Row 47
7. ROA3 = NPAT/TA = Row 112/Row 47
8. ROE = NPAT/TE = Row 112/Row 69
9. Book value of debt = Total equity + liabilities – total equity = Row 88 – Row 69
10. Sales (= Turnover) = Row 95
11. Total shares = line 5
12. Total assets = line 47
13. Total equity = line 69
14. EBITDA = line 101
15. EBIT = line 104
16. NPAT = line 112
17. Normalised earnings = line 124
18. Interim Dividend in cents per share = line 132
19. Final dividend in cents per share = line 136
20. EPS basic (cents) = line 169
21. EPS adjusted (cents) = line 170
22. Adjusted share price = line 177
The output file needs to be formatted so that the variable labels are in the columns and the year variable is in the row. Currently, the data is formatted so the fiscal year end is in the column heading and the variable labels are in the rows.
I have about 175 csv files of the same format as attached to this message. With each file I want to be able to read it into SAS and construct the following values and then save the data to a new outputted csv file.
1. Normalised earnings per share = row 124/row 5 * 100
2. Dividend payment per share per fiscal year (depends on the fiscal year of the firm, dates are shown in the file and for the attached file it is June 30 of each year ). Calculate the dividend payment = (interim dividend per share + the final dividend per share + any other dividend payments made between 1 July 19X0 and 30 June 19X1) That is the dividend per fiscal year spans two calendar year dates. See rows 167 and 171 for the interim and final dividends per share and rows 169 and 173 for the dates on which the dividend payments are made respectively.
3. Dividend payout _NORM = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/Normalised earnings per share (from (1) above)
4. Dividend payout_ADJ = (interim dividend per share + final dividend per share during the fiscal year (from (2) above))/adjusted earnings per share (row 170 on file)
5. ROA = EBIT/TA = Row 104/Row 47
6. ROA2 = EBITDA/TA = Row 101/Row 47
7. ROA3 = NPAT/TA = Row 112/Row 47
8. ROE = NPAT/TE = Row 112/Row 69
9. Book value of debt = Total equity + liabilities – total equity = Row 88 – Row 69
10. Sales (= Turnover) = Row 95
11. Total shares = line 5
12. Total assets = line 47
13. Total equity = line 69
14. EBITDA = line 101
15. EBIT = line 104
16. NPAT = line 112
17. Normalised earnings = line 124
18. Interim Dividend in cents per share = line 132
19. Final dividend in cents per share = line 136
20. EPS basic (cents) = line 169
21. EPS adjusted (cents) = line 170
22. Adjusted share price = line 177
The output file needs to be formatted so that the variable labels are in the columns and the year variable is in the row. Currently, the data is formatted so the fiscal year end is in the column heading and the variable labels are in the rows.
So what exactly is your question?
You can import the files all at once assuming a consistent format.
Usually I say keep your data long, in this case transposing to a wide format may be easier with an array.
Look at Proc Transpose.
Your question is still pretty vague so high level answers are pretty much all we can offer.
Once you have concrete questions you can get some more detailed answers.
To be honest, this forum is intended more for people who use SAS to ask questions about things they are having problems with or don't know how to do.
In the case of your post, you seem to be dropping a work unit, with the expectation that someone will develop the code to implement it at no charge.
I'll be happy to take this on for you at my usual hourly rate, but I don't expect that anyone will be interested in taking this on for free.
Tom
Let's take this offline...email me at tom.kari.consulting@bell.net
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.