how to transpose the data of multiple variables in quarterly freqency?

srikanthyadav44 — Fri, 03 Apr 2020 13:28:38 GMT

dear all,

My data set has 14 different variables of nearly 3000 companies, the variables values are collected for each quarter and variable of each quarter is in separate column.

time series- march2011 - june2011- sept 2011-dec 2011................... march2017

variables - var1 .............var14

variables are labelled as var1mar2011 - var2mar2011 and so on till var14march2017

the format of the dataset is as follows

company_name	var1mar2011	var2mar2011	……………….	var14mar2011	var1jun2011	var2jun2011	…………………	var14jun2011	…………………..	var1mar2017	var3mar2017	…………..	var14mar2017
20 Microns Ltd.	54.24	45.76		54.99	45.01	37.72		54.99		57.09	45.01		37.72
3I Infotech Ltd.	20.33	79.67		20.33	79.67	40.87		20.33		20.33	79.67		40.87
3M India Ltd.	76	24		76	24	12.87		76		76	24		12.87
3P Land Holdings Ltd.	68.99	31.01		68.99	31.01	30.97		68.99		68.99	31.01		30.97
52 Weeks Entertainment Ltd.	65.81	34.19		65.81	34.19	34.19		65.81		65.81	34.19		34.19
63 Moons Technologies Ltd.	45.39	54.2		45.39	54.27	23.67		45.39		45.69	54.27		23.67
8K Miles Software Services Ltd.	71.91	28.09		71.91	28.09	28.09		71.91		71.91	28.09		28.09
A A R V Infratel Ltd.	37.54	62.46		37.54	62.46	57.9		37.54		37.54	62.46		57.9
A B B India Ltd.	75	25		75	25	9.34		75		75	25		9.34
A B C Gas (International) Ltd.	27.06	72.94		27.71	72.29	72.29		27.71		27.71	72.29		72.29
A B C India Ltd.	54.26	45.74		55.62	44.38	44.18		55.62		58.63	44.38		44.18
A B G Shipyard Ltd.	60.72	39.28		61.67	38.33	19.09		61.67		61.67	38.33		19.09
A B M Knowledgeware Ltd.	60.02	39.98		60.02	39.98	37.7		60.02		60.02	39.98		37.7
A C C Ltd.	49.3	50.7		50.3	49.7	19.17		50.3		50.3	49.7		19.17
A C E Edutrend Ltd.	5.02	94.98		5.02	94.98	94.98		5.02		5.02	94.98		94.98
A C I Infocom Ltd.	18.39	81.61		18.39	81.61	81.61		18.39		5.48	81.61		81.61
A D C India Communications Ltd.	67.08	32.92		67.49	32.51	32.49		67.49		67.49	32.51		32.49
A D F Foods Ltd.	41.17	58.83		41.6	58.4	58.28		41.6		39.07	58.4		58.28
A D S Diagnostic Ltd.	45.23	54.77		45.23	54.77	50.19		45.23		45.23	54.77		50.19
A G C Networks Ltd.	79.13	20.87		79.13	20.87	19.08		79.13		75	20.87		19.08
A I A Engineering Ltd.	61.65	38.35		61.65	38.35	5.36		61.65		61.65	38.35		5.36
A I Champdany Inds. Ltd.	43.9	56.1		43.9	56.1	43.88		43.9		43.9	56.1		43.88
A K Capital Services Ltd.	58.67	41.33		58.67	41.33	38.21		58.67		58.67	41.33		38.21

i want the data of only march month from 2011 to 2017 in the transposed format.

I want the data set in the following format

company_name	year	var1	var2	………….	var14
20 Microns Ltd.	2011
3I Infotech Ltd.	2011
3M India Ltd.	2011
3P Land Holdings Ltd.	2011
52 Weeks Entertainment Ltd.	2011
63 Moons Technologies Ltd.	2011
8K Miles Software Services Ltd.	2011
A A R V Infratel Ltd.	2011
A B B India Ltd.	2011
A B C Gas (International) Ltd.	2011
20 Microns Ltd.	2012
3I Infotech Ltd.	2012
3M India Ltd.	2012
3P Land Holdings Ltd.	2012
52 Weeks Entertainment Ltd.	2012
63 Moons Technologies Ltd.	2012
8K Miles Software Services Ltd.	2012
A A R V Infratel Ltd.	2012
A B B India Ltd.	2012
A B C Gas (International) Ltd.	2012
20 Microns Ltd.	2013
3I Infotech Ltd.	2013
3M India Ltd.	2013
3P Land Holdings Ltd.	2013
52 Weeks Entertainment Ltd.	2013
63 Moons Technologies Ltd.	2013
8K Miles Software Services Ltd.	2013
A A R V Infratel Ltd.	2013
A B B India Ltd.	2013
A B C Gas (International) Ltd.	2013

please help in writing a SAS for this purpose

thanks in advance.

Re: how to transpose the data of multiple variables in quarterly freqency?

Tom — Fri, 03 Apr 2020 13:36:18 GMT

Transpose the data twice. In between parse the date out of the name.

Give it a shot and ask any follow up questions.

Re: how to transpose the data of multiple variables in quarterly freqency?

Reeza — Fri, 03 Apr 2020 16:20:18 GMT

Tom is correct, first flip to a long format and then flip back to a wide format with the stocks.

I'd suggest that you may want to consider leaving it fully long data set though.

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

Re: how to transpose the data of multiple variables in quarterly freqency?

art297 — Fri, 03 Apr 2020 21:29:32 GMT

Easily done with the %untranspose macro. e.g.:

filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=have, out=want, by=company_name,
  id=date,id_informat=monyy7.,
  var=var1-var14, var_first=yes)

data want (drop=date where=(month eq 3));;
  set want;
  format date monyy7.;
  year=year(date);
  month=month(date);
run;

Art, CEO, AnalystFinder.com

topic how to transpose the data of multiple variables in quarterly freqency? in SAS Programming

how to transpose the data of multiple variables in quarterly freqency?

Re: how to transpose the data of multiple variables in quarterly freqency?

Re: how to transpose the data of multiple variables in quarterly freqency?

Re: how to transpose the data of multiple variables in quarterly freqency?