BookmarkSubscribeRSS Feed
Jahanzaib
Quartz | Level 8

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

3 REPLIES 3
Reeza
Super User

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.

Jahanzaib
Quartz | Level 8

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

Reeza
Super User

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

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 840 views
  • 3 likes
  • 2 in conversation