BookmarkSubscribeRSS Feed
Doe
Calcite | Level 5 Doe
Calcite | Level 5

I have a  data the below below format:

 CodeCompany NameItem2019
DINDIN LimitedAnnual Balance Sheet - Total Equity16,925,931.00
DINDIN LimitedAnnual Balance Sheet - NCA PP&E135,487.00
DINDIN LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)5,109,045.00
DINDIN LimitedAnnual Balance Sheet - NCA Goodwill0.00
DINDIN LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-4,270,815.00
DINDIN LimitedAnnual Profit and Loss - Total Revenue Excluding Int60,273.00
DINDIN LimitedAnnual Profit and Loss - Shares Outstanding at Perio172,389,923.00
DINDIN LimitedAnnual Ratio Analysis - Market Cap.($)50,855,027.28

 

I would like to convert to a wide format as shown below:

 CodeCompany NameAnnual Balance Sheet - Total EquityAnnual Balance Sheet - NCA PP&EAnnual Balance Sheet - NCA Intangibles(ExGW)Annual Balance Sheet - NCA GoodwillAnnual Profit and Loss - Reported NPAT After AbnormaAnnual Profit and Loss - Shares Outstanding at PerioAnnual Ratio Analysis - Market Cap.($)
DINDIN Limited16,925,931.00135,487.005,109,045.000.00-4,270,815.00172,389,923.0050,855,027.28
         
9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

Why do you want this? Is this for reporting purposes? 

Doe
Calcite | Level 5 Doe
Calcite | Level 5

I want to be able to generate descriptive statistics for the individual variables( Annual Balance Sheet-Total Equity, Annual Balance Sheet-NCA PP&E, Annual Balance Sheet-NCA Intangibles(ExGW)...) under ITEM

 

PeterClemmensen
Tourmaline | Level 20

You do not need a wide format to do that. Please show us your desired descriptive statistics from the posted data.

Doe
Calcite | Level 5 Doe
Calcite | Level 5

What I am seeking to do is to be able to generate descriptive statistics for the various variables under ITEM. For example the mean, median for Annual Balance Sheet-Total Equity . This is the original data: 

ASX CodeCompany NameItem2019
14D1414 Degrees LimitedAnnual Balance Sheet - Total Equity16,925,931.00
14D1414 Degrees LimitedAnnual Balance Sheet - NCA PP&E135,487.00
14D1414 Degrees LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)5,109,045.00
14D1414 Degrees LimitedAnnual Balance Sheet - NCA Goodwill0.00
14D1414 Degrees LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-4,270,815.00
14D1414 Degrees LimitedAnnual Profit and Loss - Total Revenue Excluding Int60,273.00
14D1414 Degrees LimitedAnnual Profit and Loss - Shares Outstanding at Perio172,389,923.00
14D1414 Degrees LimitedAnnual Ratio Analysis - Market Cap.($)50,855,027.28
1ADAdAlta LimitedAnnual Balance Sheet - Total Equity7,491,109.00
1ADAdAlta LimitedAnnual Balance Sheet - NCA PP&E138,105.00
1ADAdAlta LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)0.00
1ADAdAlta LimitedAnnual Balance Sheet - NCA Goodwill0.00
1ADAdAlta LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-5,918,193.00
1ADAdAlta LimitedAnnual Profit and Loss - Total Revenue Excluding Int3,538,774.00
1ADAdAlta LimitedAnnual Profit and Loss - Shares Outstanding at Perio150,569,426.00
1ADAdAlta LimitedAnnual Ratio Analysis - Market Cap.($)24,091,108.16
1AGAlterra LimitedAnnual Balance Sheet - Total Equity6,470,870.00
1AGAlterra LimitedAnnual Balance Sheet - NCA PP&E72,894.00
1AGAlterra LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)0.00
1AGAlterra LimitedAnnual Balance Sheet - NCA Goodwill0.00
1AGAlterra LimitedAnnual Profit and Loss - Reported NPAT After Abnorma931,424.00
1AGAlterra LimitedAnnual Profit and Loss - Total Revenue Excluding Int754,415.00
1AGAlterra LimitedAnnual Profit and Loss - Shares Outstanding at Perio148,777,688.00
1AGAlterra LimitedAnnual Ratio Analysis - Market Cap.($)5,653,552.14
1ALOneAll International LimitedAnnual Balance Sheet - Total Equity25,429,610.00
1ALOneAll International LimitedAnnual Balance Sheet - NCA PP&E11,776,045.00
1ALOneAll International LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)21,360.00
1ALOneAll International LimitedAnnual Balance Sheet - NCA Goodwill0.00
1ALOneAll International LimitedAnnual Profit and Loss - Reported NPAT After Abnorma2,090,162.00
1ALOneAll International LimitedAnnual Profit and Loss - Total Revenue Excluding Int48,520,677.00
1ALOneAll International LimitedAnnual Profit and Loss - Shares Outstanding at Perio130,015,920.00
1ALOneAll International LimitedAnnual Ratio Analysis - Market Cap.($)111,813,691.20
1ST1ST Group LimitedAnnual Balance Sheet - Total Equity3,130,856.00
1ST1ST Group LimitedAnnual Balance Sheet - NCA PP&E32,140.00
1ST1ST Group LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)961,872.00
1ST1ST Group LimitedAnnual Balance Sheet - NCA Goodwill2,500,456.00
1ST1ST Group LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-4,304,092.00
1ST1ST Group LimitedAnnual Profit and Loss - Total Revenue Excluding Int3,958,459.00
1ST1ST Group LimitedAnnual Profit and Loss - Shares Outstanding at Perio355,386,134.00
1ST1ST Group LimitedAnnual Ratio Analysis - Market Cap.($)24,166,257.11
2BETubi LimitedAnnual Balance Sheet - Total Equity21,628,097.00
2BETubi LimitedAnnual Balance Sheet - NCA PP&E16,814,696.00
2BETubi LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)275,223.00
2BETubi LimitedAnnual Balance Sheet - NCA Goodwill0.00
2BETubi LimitedAnnual Profit and Loss - Reported NPAT After Abnorma1,499,053.00
2BETubi LimitedAnnual Profit and Loss - Total Revenue Excluding Int31,661,019.00
2BETubi LimitedAnnual Profit and Loss - Shares Outstanding at Perio243,142,400.00
2BETubi LimitedAnnual Ratio Analysis - Market Cap.($)65,648,448.00
360Life360 IncAnnual Balance Sheet - Total Equity103,163,003.00
360Life360 IncAnnual Balance Sheet - NCA PP&E780,759.00
360Life360 IncAnnual Balance Sheet - NCA Intangibles(ExGW)366,828.00
360Life360 IncAnnual Balance Sheet - NCA Goodwill1,090,493.00
360Life360 IncAnnual Profit and Loss - Reported NPAT After Abnorma-41,326,006.00
360Life360 IncAnnual Profit and Loss - Total Revenue Excluding Int85,365,401.00
360Life360 IncAnnual Profit and Loss - Shares Outstanding at Perio146,492,067.00
360Life360 IncAnnual Ratio Analysis - Market Cap.($)461,450,011.05
ballardw
Super User

Is the purpose of the transform to make something to read? Or analysis?

If you want a report that people will read then proc report is likely the easiest

With something like

Proc report data=have;
   columns code companyname item,<name of numeric variable goes here>;
   define code /group;
   define companyname /group;
   define item / across;
   define <name of numeric variable goes here> /format comma16.2;
run;

 

Since you show to column headers that are not typically valid SAS variable names, "Company Name" and "2019" I have to ask what your actual variable names are as well.

The proposed structure has multiple column headings that cannot be valid variable names but could be used as labels.

If you really need a data set then perhaps, if the data is sorted by code and company name

proc transpose data=have out=want;
   by code companyname;
   idlabel item;
   var <name of numeric variable goes here>;
run;
Doe
Calcite | Level 5 Doe
Calcite | Level 5

2019 shows the figures for year 2019. My aim is to transform for analysis for each company. This is the full data.

 

ASX CodeCompany NameItem2019
14D1414 Degrees LimitedAnnual Balance Sheet - Total Equity16,925,931.00
14D1414 Degrees LimitedAnnual Balance Sheet - NCA PP&E135,487.00
14D1414 Degrees LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)5,109,045.00
14D1414 Degrees LimitedAnnual Balance Sheet - NCA Goodwill0.00
14D1414 Degrees LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-4,270,815.00
14D1414 Degrees LimitedAnnual Profit and Loss - Total Revenue Excluding Int60,273.00
14D1414 Degrees LimitedAnnual Profit and Loss - Shares Outstanding at Perio172,389,923.00
14D1414 Degrees LimitedAnnual Ratio Analysis - Market Cap.($)50,855,027.28
1ADAdAlta LimitedAnnual Balance Sheet - Total Equity7,491,109.00
1ADAdAlta LimitedAnnual Balance Sheet - NCA PP&E138,105.00
1ADAdAlta LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)0.00
1ADAdAlta LimitedAnnual Balance Sheet - NCA Goodwill0.00
1ADAdAlta LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-5,918,193.00
1ADAdAlta LimitedAnnual Profit and Loss - Total Revenue Excluding Int3,538,774.00
1ADAdAlta LimitedAnnual Profit and Loss - Shares Outstanding at Perio150,569,426.00
1ADAdAlta LimitedAnnual Ratio Analysis - Market Cap.($)24,091,108.16
1AGAlterra LimitedAnnual Balance Sheet - Total Equity6,470,870.00
1AGAlterra LimitedAnnual Balance Sheet - NCA PP&E72,894.00
1AGAlterra LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)0.00
1AGAlterra LimitedAnnual Balance Sheet - NCA Goodwill0.00
1AGAlterra LimitedAnnual Profit and Loss - Reported NPAT After Abnorma931,424.00
1AGAlterra LimitedAnnual Profit and Loss - Total Revenue Excluding Int754,415.00
1AGAlterra LimitedAnnual Profit and Loss - Shares Outstanding at Perio148,777,688.00
1AGAlterra LimitedAnnual Ratio Analysis - Market Cap.($)5,653,552.14
1ALOneAll International LimitedAnnual Balance Sheet - Total Equity25,429,610.00
1ALOneAll International LimitedAnnual Balance Sheet - NCA PP&E11,776,045.00
1ALOneAll International LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)21,360.00
1ALOneAll International LimitedAnnual Balance Sheet - NCA Goodwill0.00
1ALOneAll International LimitedAnnual Profit and Loss - Reported NPAT After Abnorma2,090,162.00
1ALOneAll International LimitedAnnual Profit and Loss - Total Revenue Excluding Int48,520,677.00
1ALOneAll International LimitedAnnual Profit and Loss - Shares Outstanding at Perio130,015,920.00
1ALOneAll International LimitedAnnual Ratio Analysis - Market Cap.($)111,813,691.20
1ST1ST Group LimitedAnnual Balance Sheet - Total Equity3,130,856.00
1ST1ST Group LimitedAnnual Balance Sheet - NCA PP&E32,140.00
1ST1ST Group LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)961,872.00
1ST1ST Group LimitedAnnual Balance Sheet - NCA Goodwill2,500,456.00
1ST1ST Group LimitedAnnual Profit and Loss - Reported NPAT After Abnorma-4,304,092.00
1ST1ST Group LimitedAnnual Profit and Loss - Total Revenue Excluding Int3,958,459.00
1ST1ST Group LimitedAnnual Profit and Loss - Shares Outstanding at Perio355,386,134.00
1ST1ST Group LimitedAnnual Ratio Analysis - Market Cap.($)24,166,257.11
2BETubi LimitedAnnual Balance Sheet - Total Equity21,628,097.00
2BETubi LimitedAnnual Balance Sheet - NCA PP&E16,814,696.00
2BETubi LimitedAnnual Balance Sheet - NCA Intangibles(ExGW)275,223.00
2BETubi LimitedAnnual Balance Sheet - NCA Goodwill0.00
2BETubi LimitedAnnual Profit and Loss - Reported NPAT After Abnorma1,499,053.00
2BETubi LimitedAnnual Profit and Loss - Total Revenue Excluding Int31,661,019.00
2BETubi LimitedAnnual Profit and Loss - Shares Outstanding at Perio243,142,400.00
2BETubi LimitedAnnual Ratio Analysis - Market Cap.($)65,648,448.00
360Life360 IncAnnual Balance Sheet - Total Equity103,163,003.00
360Life360 IncAnnual Balance Sheet - NCA PP&E780,759.00
360Life360 IncAnnual Balance Sheet - NCA Intangibles(ExGW)366,828.00
360Life360 IncAnnual Balance Sheet - NCA Goodwill1,090,493.00
360Life360 IncAnnual Profit and Loss - Reported NPAT After Abnorma-41,326,006.00
360Life360 IncAnnual Profit and Loss - Total Revenue Excluding Int85,365,401.00
360Life360 IncAnnual Profit and Loss - Shares Outstanding at Perio146,492,067.00
360Life360 IncAnnual Ratio Analysis - Market Cap.($)461,450,011.05
Doe
Calcite | Level 5 Doe
Calcite | Level 5

what does n represent?

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
  • 9 replies
  • 1132 views
  • 1 like
  • 4 in conversation