topic Converting from Long to Wide Format in SAS Programming

Converting from Long to Wide Format

Doe — Tue, 16 Feb 2021 09:54:28 GMT

I have a data the below below format:

Code	Company Name	Item	2019
DIN	DIN Limited	Annual Balance Sheet - Total Equity	16,925,931.00
DIN	DIN Limited	Annual Balance Sheet - NCA PP&E	135,487.00
DIN	DIN Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	5,109,045.00
DIN	DIN Limited	Annual Balance Sheet - NCA Goodwill	0.00
DIN	DIN Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-4,270,815.00
DIN	DIN Limited	Annual Profit and Loss - Total Revenue Excluding Int	60,273.00
DIN	DIN Limited	Annual Profit and Loss - Shares Outstanding at Perio	172,389,923.00
DIN	DIN Limited	Annual Ratio Analysis - Market Cap.($)	50,855,027.28

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

Code	Company Name	Annual Balance Sheet - Total Equity	Annual Balance Sheet - NCA PP&E	Annual Balance Sheet - NCA Intangibles(ExGW)	Annual Balance Sheet - NCA Goodwill	Annual Profit and Loss - Reported NPAT After Abnorma	Annual Profit and Loss - Shares Outstanding at Perio	Annual Ratio Analysis - Market Cap.($)
DIN	DIN Limited	16,925,931.00	135,487.00	5,109,045.00	0.00	-4,270,815.00	172,389,923.00	50,855,027.28

Re: Converting from Long to Wide Format

PeterClemmensen — Tue, 16 Feb 2021 10:05:20 GMT

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

Re: Converting from Long to Wide Format

Doe — Tue, 16 Feb 2021 10:10:44 GMT

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

Re: Converting from Long to Wide Format

PeterClemmensen — Tue, 16 Feb 2021 10:14:01 GMT

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

Re: Converting from Long to Wide Format

ballardw — Tue, 16 Feb 2021 10:14:49 GMT

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;

Re: Converting from Long to Wide Format

Kurt_Bremser — Tue, 16 Feb 2021 10:19:48 GMT

You don't need a transpose, see this example:

proc summary data=have;
class item;
var '2019'n;
output out=want sum()=;
run;

Re: Converting from Long to Wide Format

Doe — Tue, 16 Feb 2021 10:32:47 GMT

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

ASX Code	Company Name	Item	2019
14D	1414 Degrees Limited	Annual Balance Sheet - Total Equity	16,925,931.00
14D	1414 Degrees Limited	Annual Balance Sheet - NCA PP&E	135,487.00
14D	1414 Degrees Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	5,109,045.00
14D	1414 Degrees Limited	Annual Balance Sheet - NCA Goodwill	0.00
14D	1414 Degrees Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-4,270,815.00
14D	1414 Degrees Limited	Annual Profit and Loss - Total Revenue Excluding Int	60,273.00
14D	1414 Degrees Limited	Annual Profit and Loss - Shares Outstanding at Perio	172,389,923.00
14D	1414 Degrees Limited	Annual Ratio Analysis - Market Cap.($)	50,855,027.28
1AD	AdAlta Limited	Annual Balance Sheet - Total Equity	7,491,109.00
1AD	AdAlta Limited	Annual Balance Sheet - NCA PP&E	138,105.00
1AD	AdAlta Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	0.00
1AD	AdAlta Limited	Annual Balance Sheet - NCA Goodwill	0.00
1AD	AdAlta Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-5,918,193.00
1AD	AdAlta Limited	Annual Profit and Loss - Total Revenue Excluding Int	3,538,774.00
1AD	AdAlta Limited	Annual Profit and Loss - Shares Outstanding at Perio	150,569,426.00
1AD	AdAlta Limited	Annual Ratio Analysis - Market Cap.($)	24,091,108.16
1AG	Alterra Limited	Annual Balance Sheet - Total Equity	6,470,870.00
1AG	Alterra Limited	Annual Balance Sheet - NCA PP&E	72,894.00
1AG	Alterra Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	0.00
1AG	Alterra Limited	Annual Balance Sheet - NCA Goodwill	0.00
1AG	Alterra Limited	Annual Profit and Loss - Reported NPAT After Abnorma	931,424.00
1AG	Alterra Limited	Annual Profit and Loss - Total Revenue Excluding Int	754,415.00
1AG	Alterra Limited	Annual Profit and Loss - Shares Outstanding at Perio	148,777,688.00
1AG	Alterra Limited	Annual Ratio Analysis - Market Cap.($)	5,653,552.14
1AL	OneAll International Limited	Annual Balance Sheet - Total Equity	25,429,610.00
1AL	OneAll International Limited	Annual Balance Sheet - NCA PP&E	11,776,045.00
1AL	OneAll International Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	21,360.00
1AL	OneAll International Limited	Annual Balance Sheet - NCA Goodwill	0.00
1AL	OneAll International Limited	Annual Profit and Loss - Reported NPAT After Abnorma	2,090,162.00
1AL	OneAll International Limited	Annual Profit and Loss - Total Revenue Excluding Int	48,520,677.00
1AL	OneAll International Limited	Annual Profit and Loss - Shares Outstanding at Perio	130,015,920.00
1AL	OneAll International Limited	Annual Ratio Analysis - Market Cap.($)	111,813,691.20
1ST	1ST Group Limited	Annual Balance Sheet - Total Equity	3,130,856.00
1ST	1ST Group Limited	Annual Balance Sheet - NCA PP&E	32,140.00
1ST	1ST Group Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	961,872.00
1ST	1ST Group Limited	Annual Balance Sheet - NCA Goodwill	2,500,456.00
1ST	1ST Group Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-4,304,092.00
1ST	1ST Group Limited	Annual Profit and Loss - Total Revenue Excluding Int	3,958,459.00
1ST	1ST Group Limited	Annual Profit and Loss - Shares Outstanding at Perio	355,386,134.00
1ST	1ST Group Limited	Annual Ratio Analysis - Market Cap.($)	24,166,257.11
2BE	Tubi Limited	Annual Balance Sheet - Total Equity	21,628,097.00
2BE	Tubi Limited	Annual Balance Sheet - NCA PP&E	16,814,696.00
2BE	Tubi Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	275,223.00
2BE	Tubi Limited	Annual Balance Sheet - NCA Goodwill	0.00
2BE	Tubi Limited	Annual Profit and Loss - Reported NPAT After Abnorma	1,499,053.00
2BE	Tubi Limited	Annual Profit and Loss - Total Revenue Excluding Int	31,661,019.00
2BE	Tubi Limited	Annual Profit and Loss - Shares Outstanding at Perio	243,142,400.00
2BE	Tubi Limited	Annual Ratio Analysis - Market Cap.($)	65,648,448.00
360	Life360 Inc	Annual Balance Sheet - Total Equity	103,163,003.00
360	Life360 Inc	Annual Balance Sheet - NCA PP&E	780,759.00
360	Life360 Inc	Annual Balance Sheet - NCA Intangibles(ExGW)	366,828.00
360	Life360 Inc	Annual Balance Sheet - NCA Goodwill	1,090,493.00
360	Life360 Inc	Annual Profit and Loss - Reported NPAT After Abnorma	-41,326,006.00
360	Life360 Inc	Annual Profit and Loss - Total Revenue Excluding Int	85,365,401.00
360	Life360 Inc	Annual Profit and Loss - Shares Outstanding at Perio	146,492,067.00
360	Life360 Inc	Annual Ratio Analysis - Market Cap.($)	461,450,011.05

Re: Converting from Long to Wide Format

Doe — Tue, 16 Feb 2021 10:37:52 GMT

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 Code	Company Name	Item	2019
14D	1414 Degrees Limited	Annual Balance Sheet - Total Equity	16,925,931.00
14D	1414 Degrees Limited	Annual Balance Sheet - NCA PP&E	135,487.00
14D	1414 Degrees Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	5,109,045.00
14D	1414 Degrees Limited	Annual Balance Sheet - NCA Goodwill	0.00
14D	1414 Degrees Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-4,270,815.00
14D	1414 Degrees Limited	Annual Profit and Loss - Total Revenue Excluding Int	60,273.00
14D	1414 Degrees Limited	Annual Profit and Loss - Shares Outstanding at Perio	172,389,923.00
14D	1414 Degrees Limited	Annual Ratio Analysis - Market Cap.($)	50,855,027.28
1AD	AdAlta Limited	Annual Balance Sheet - Total Equity	7,491,109.00
1AD	AdAlta Limited	Annual Balance Sheet - NCA PP&E	138,105.00
1AD	AdAlta Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	0.00
1AD	AdAlta Limited	Annual Balance Sheet - NCA Goodwill	0.00
1AD	AdAlta Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-5,918,193.00
1AD	AdAlta Limited	Annual Profit and Loss - Total Revenue Excluding Int	3,538,774.00
1AD	AdAlta Limited	Annual Profit and Loss - Shares Outstanding at Perio	150,569,426.00
1AD	AdAlta Limited	Annual Ratio Analysis - Market Cap.($)	24,091,108.16
1AG	Alterra Limited	Annual Balance Sheet - Total Equity	6,470,870.00
1AG	Alterra Limited	Annual Balance Sheet - NCA PP&E	72,894.00
1AG	Alterra Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	0.00
1AG	Alterra Limited	Annual Balance Sheet - NCA Goodwill	0.00
1AG	Alterra Limited	Annual Profit and Loss - Reported NPAT After Abnorma	931,424.00
1AG	Alterra Limited	Annual Profit and Loss - Total Revenue Excluding Int	754,415.00
1AG	Alterra Limited	Annual Profit and Loss - Shares Outstanding at Perio	148,777,688.00
1AG	Alterra Limited	Annual Ratio Analysis - Market Cap.($)	5,653,552.14
1AL	OneAll International Limited	Annual Balance Sheet - Total Equity	25,429,610.00
1AL	OneAll International Limited	Annual Balance Sheet - NCA PP&E	11,776,045.00
1AL	OneAll International Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	21,360.00
1AL	OneAll International Limited	Annual Balance Sheet - NCA Goodwill	0.00
1AL	OneAll International Limited	Annual Profit and Loss - Reported NPAT After Abnorma	2,090,162.00
1AL	OneAll International Limited	Annual Profit and Loss - Total Revenue Excluding Int	48,520,677.00
1AL	OneAll International Limited	Annual Profit and Loss - Shares Outstanding at Perio	130,015,920.00
1AL	OneAll International Limited	Annual Ratio Analysis - Market Cap.($)	111,813,691.20
1ST	1ST Group Limited	Annual Balance Sheet - Total Equity	3,130,856.00
1ST	1ST Group Limited	Annual Balance Sheet - NCA PP&E	32,140.00
1ST	1ST Group Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	961,872.00
1ST	1ST Group Limited	Annual Balance Sheet - NCA Goodwill	2,500,456.00
1ST	1ST Group Limited	Annual Profit and Loss - Reported NPAT After Abnorma	-4,304,092.00
1ST	1ST Group Limited	Annual Profit and Loss - Total Revenue Excluding Int	3,958,459.00
1ST	1ST Group Limited	Annual Profit and Loss - Shares Outstanding at Perio	355,386,134.00
1ST	1ST Group Limited	Annual Ratio Analysis - Market Cap.($)	24,166,257.11
2BE	Tubi Limited	Annual Balance Sheet - Total Equity	21,628,097.00
2BE	Tubi Limited	Annual Balance Sheet - NCA PP&E	16,814,696.00
2BE	Tubi Limited	Annual Balance Sheet - NCA Intangibles(ExGW)	275,223.00
2BE	Tubi Limited	Annual Balance Sheet - NCA Goodwill	0.00
2BE	Tubi Limited	Annual Profit and Loss - Reported NPAT After Abnorma	1,499,053.00
2BE	Tubi Limited	Annual Profit and Loss - Total Revenue Excluding Int	31,661,019.00
2BE	Tubi Limited	Annual Profit and Loss - Shares Outstanding at Perio	243,142,400.00
2BE	Tubi Limited	Annual Ratio Analysis - Market Cap.($)	65,648,448.00
360	Life360 Inc	Annual Balance Sheet - Total Equity	103,163,003.00
360	Life360 Inc	Annual Balance Sheet - NCA PP&E	780,759.00
360	Life360 Inc	Annual Balance Sheet - NCA Intangibles(ExGW)	366,828.00
360	Life360 Inc	Annual Balance Sheet - NCA Goodwill	1,090,493.00
360	Life360 Inc	Annual Profit and Loss - Reported NPAT After Abnorma	-41,326,006.00
360	Life360 Inc	Annual Profit and Loss - Total Revenue Excluding Int	85,365,401.00
360	Life360 Inc	Annual Profit and Loss - Shares Outstanding at Perio	146,492,067.00
360	Life360 Inc	Annual Ratio Analysis - Market Cap.($)	461,450,011.05

Re: Converting from Long to Wide Format

Doe — Tue, 16 Feb 2021 10:40:27 GMT

what does n represent?

Re: Converting from Long to Wide Format

Kurt_Bremser — Tue, 16 Feb 2021 10:43:58 GMT

Since "2019" is not a valid SAS name, we have to use a "name literal" and

options validvarname=any;

_2019, OTOH, would be a valid SAS name.