topic Re: Need help for setting Data in SAS Data Management

Need help for setting Data

Jahanzaib — Tue, 23 Dec 2014 05:02:42 GMT

The file Brazil contain data of Brazil, I have data of more than 30 countries in separate files. I want to apply Difference in Difference technique on this data. First I think I have to combine all these files. I have downloaded all this data from Data stream. Now I want to go by this way

(Brazil file attached)In Column “C” name EMBRAER-AERONAUTICA - RETURN ON ASSETS goes under ROA, in the same way Column “V” name FORJAS TAURUS - RETURN ON ASSETS goes under ROA. Column “AO” is VANGUARDA AGRO - RETURN ON ASSETS it also go under ROA as it is Return on Assets but first name of all these columns contain company name first 4 alphabets are enough to be placed in the column of Firm as giving in the sample file.
In the same way other columns goes by following way. Column which contain word

- TOTAL DEBT goes under TDT

-CURRENT ASSETS – TOTAL goes under CA

-CURRENT LIABILITIES-TOTAL goes under CL

- TOTAL ASSETS goes under TA

- TOTAL CAPITAL goes under TC

-COMMON SHAREHOLDERS' EQUITY goes under CSE

- LONG TERM DEBT goes under LTDT

-TOTAL DEBT % COMMON EQUITY goes under TD/EQ

-TOTAL DEBT % TOTAL CAPITAL/STD goes under TD/TC

- TOTAL DEBT % TOTAL ASSETS goes under TD/TA

- EARNINGS BEF INTEREST & TAXES goes under EBIT

- OPERATING INCOME goes under OPIN

- DIVIDENDS PER SHARE goes under DPS

-- TOTAL SHAREHOLDERS EQUITY goes under SHE

- MRKT VALUE TO BOOK goes under M/B

- MARKET VALUE goes under MV

- EARNINGS PER SHR goes under EPS

Re: Need help for setting Data

Reeza — Tue, 23 Dec 2014 06:09:45 GMT

You should transpose your data before you bring it in to SAS, there's often times issues in importing excel worksheets that are more than 256 columns.

Additionally, It will make it easier to transform the data to your desired format.

In Excel you can copy>Paste Special>Transpose to achieve this.

The you can use IF statements to rename the variables and separate out company names from variable names.

Once that's done you can re-transpose using Proc Transpose to get your final dataset.

Re: Need help for setting Data

Jahanzaib — Tue, 23 Dec 2014 07:13:33 GMT

Can you please tell me which If statement i have to use for separating company name from Variable name?

Re: Need help for setting Data

Reeza — Tue, 23 Dec 2014 17:44:17 GMT

Use the scan function as it appears the company name is separated from the variable by using a hyphen.

company_name=scan(name, 1, "-");

variable_name=scan(name, 2, "-");

Here's the reference to the Scan function in SAS

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition