BookmarkSubscribeRSS Feed
srikanthyadav44
Quartz | Level 8

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_namevar1mar2011var2mar2011……………….var14mar2011var1jun2011var2jun2011…………………var14jun2011…………………..var1mar2017var3mar2017…………..var14mar2017
20 Microns Ltd.54.2445.76 54.9945.0137.72 54.99 57.0945.01 37.72
3I Infotech Ltd.20.3379.67 20.3379.6740.87 20.33 20.3379.67 40.87
3M India Ltd.7624 762412.87 76 7624 12.87
3P Land Holdings Ltd.68.9931.01 68.9931.0130.97 68.99 68.9931.01 30.97
52 Weeks Entertainment Ltd.65.8134.19 65.8134.1934.19 65.81 65.8134.19 34.19
63 Moons Technologies Ltd.45.3954.2 45.3954.2723.67 45.39 45.6954.27 23.67
8K Miles Software Services Ltd.71.9128.09 71.9128.0928.09 71.91 71.9128.09 28.09
A A R V Infratel Ltd.37.5462.46 37.5462.4657.9 37.54 37.5462.46 57.9
A B B India Ltd.7525 75259.34 75 7525 9.34
A B C Gas (International) Ltd.27.0672.94 27.7172.2972.29 27.71 27.7172.29 72.29
A B C India Ltd.54.2645.74 55.6244.3844.18 55.62 58.6344.38 44.18
A B G Shipyard Ltd.60.7239.28 61.6738.3319.09 61.67 61.6738.33 19.09
A B M Knowledgeware Ltd.60.0239.98 60.0239.9837.7 60.02 60.0239.98 37.7
A C C Ltd.49.350.7 50.349.719.17 50.3 50.349.7 19.17
A C E Edutrend Ltd.5.0294.98 5.0294.9894.98 5.02 5.0294.98 94.98
A C I Infocom Ltd.18.3981.61 18.3981.6181.61 18.39 5.4881.61 81.61
A D C India Communications Ltd.67.0832.92 67.4932.5132.49 67.49 67.4932.51 32.49
A D F Foods Ltd.41.1758.83 41.658.458.28 41.6 39.0758.4 58.28
A D S Diagnostic Ltd.45.2354.77 45.2354.7750.19 45.23 45.2354.77 50.19
A G C Networks Ltd.79.1320.87 79.1320.8719.08 79.13 7520.87 19.08
A I A Engineering Ltd.61.6538.35 61.6538.355.36 61.65 61.6538.35 5.36
A I Champdany Inds. Ltd.43.956.1 43.956.143.88 43.9 43.956.1 43.88
A K Capital Services Ltd.58.6741.33 58.6741.3338.21 58.67 58.6741.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_nameyearvar1var2………….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. 

 

 

 

3 REPLIES 3
Tom
Super User Tom
Super User

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

Give it a shot and ask any follow up questions.

Reeza
Super User

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/

art297
Opal | Level 21

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 483 views
  • 0 likes
  • 4 in conversation