BookmarkSubscribeRSS Feed
Kachiun
Calcite | Level 5

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.

8 REPLIES 8
Kachiun
Calcite | Level 5

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.

Reeza
Super User

So what exactly is your question? 

 

Kachiun
Calcite | Level 5
Sorry, I guess I didn't make it very clear. I have outlined all the processes that I will do at a later date. I am asking how to format the data so SAS understands it. So I have 175 files that I need to import and (from my understanding) transpose to then conduct the above analysis. How would I best go about this?
Reeza
Super User

You can import the files all at once assuming a consistent format. 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

 

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. 

TomKari
Onyx | Level 15

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

Kachiun
Calcite | Level 5
How much would you charge to complete this process? I understand you work hourly but would you consider a detailing a quote for me?
TomKari
Onyx | Level 15

Let's take this offline...email me at tom.kari.consulting@bell.net

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1642 views
  • 2 likes
  • 4 in conversation