Dear all,
Hope you can help me on this since I am using a visual basics code to transpose data and it takes ages. I have data in excel and the first column contains the date and the first row contains the stock codes and all the other cells have market values for each stock for a specific date. Is there any way to transpose it into three columns, first date, second column stock code and third column market value.
For example the input will be
Code | ID:AAL | ID:AAP | ID:ABC | ID:ABD | ID:ABN | ID:ACA |
31/3/2004 | 145.86 | 53.85 | 100 | 123.43 | 214.42 | 235.45 |
30/4/2004 | 172.04 | 56.77 | 100 | 123.43 | 214.42 | 240.4 |
And I am looking for thefollowing out put,
31/3/2004 | ID:AAL | 145.86 |
31/3/2004 | ID:AAP | 53.85 |
31/3/2004 | ID:ABC | 100 |
31/3/2004 | ID:ABD | 123.43 |
31/3/2004 | ID:ABN | 214.42 |
31/3/2004 | ID:ACA | 235.45 |
30/4/2004 | ID:AAL | 172.04 |
30/4/2004 | ID:AAP | 56.77 |
30/4/2004 | ID:ABC | 100 |
30/4/2004 | ID:ABD | 123.43 |
30/4/2004 | ID:ABN | 214.42 |
30/4/2004 | ID:ACA | 240.4 |
Please keep in mind that I have thousands of stocks using daily data for 30 years.
Thanks for your help.
Best,
Cheema
Transposing data with in multiple columns into three columns only [ New ]
inspired by
https://goo.gl/MqqEhO
https://communities.sas.com/t5/Base-SAS-Programming/Transposing-data-with-in-multiple-columns-into-three-columns/m-p/348400
HAVE (Tiny Fat dataset of 2000 stocks for every day fro 30 years)
=================================================================
Middle Observation(5661 ) of have - Total Obs 11,323
TOTOBS C 16 11,323
-- NUMERIC --
DTE N 8 01JAN1974
STOCK1 N 8 188.87
STOCK2 N 8 73.72
STOCK3 N 8 83.28
STOCK4 N 8 4.83
STOCK5 N 8 19.309
STOCK6 N 8 193.59
...
STOCK1997 N 8 54.62
STOCK1998 N 8 14.25
STOCK1999 N 8 66.88
STOCK2000 N 8 176.48
WANT (Normalixed data)
Up to 40 obs from want total obs=22,646,000
STOCK_ STOCK_
Obs DTE NAME VALUE
1 01JAN1974 STOCK1 4.098
2 01JAN1974 STOCK2 115.686
3 01JAN1974 STOCK3 78.620
4 01JAN1974 STOCK4 122.727
5 01JAN1974 STOCK5 140.875
6 01JAN1974 STOCK6 185.698
7 01JAN1974 STOCK7 162.728
8 01JAN1974 STOCK8 25.053
9 01JAN1974 STOCK9 73.913
10 01JAN1974 STOCK10 98.581
WORKING CODE
============
do over stocks;
stock_name=vname(stocks);
stock_value=stocks;
output;
FULL SOLUTION
=============
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
data have;
retain dte;
array stocks stock1-stock2000;
do dte='01JAN1974'd to '31DEC2004'd;
do over stocks;
stocks=200*uniform(5731);
end;
output;
end;
run;quit;
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
data want;
set have;
array stocks stock1-stock2000;
do over stocks;
stock_name=vname(stocks);
stock_value=stocks;
output;
end;
drop stock1-stock2000;
run;quit;
313 data want;
314 set have;
315 array stocks stock1-stock2000;
316 do over stocks;
317 stock_name=vname(stocks);
318 stock_value=stocks;
319 output;
320 end;
321 drop stock1-stock2000;
322 run;
NOTE: There were 11323 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 22646000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 10.58 seconds
cpu time 10.12 seconds
proc print data=want(obs=10) width=min;
format dte date9.;
run;quit;
Transposing data with in multiple columns into three columns only [ New ]
inspired by
https://goo.gl/MqqEhO
https://communities.sas.com/t5/Base-SAS-Programming/Transposing-data-with-in-multiple-columns-into-three-columns/m-p/348400
HAVE (Tiny Fat dataset of 2000 stocks for every day fro 30 years)
=================================================================
Middle Observation(5661 ) of have - Total Obs 11,323
TOTOBS C 16 11,323
-- NUMERIC --
DTE N 8 01JAN1974
STOCK1 N 8 188.87
STOCK2 N 8 73.72
STOCK3 N 8 83.28
STOCK4 N 8 4.83
STOCK5 N 8 19.309
STOCK6 N 8 193.59
...
STOCK1997 N 8 54.62
STOCK1998 N 8 14.25
STOCK1999 N 8 66.88
STOCK2000 N 8 176.48
WANT (Normalixed data)
Up to 40 obs from want total obs=22,646,000
STOCK_ STOCK_
Obs DTE NAME VALUE
1 01JAN1974 STOCK1 4.098
2 01JAN1974 STOCK2 115.686
3 01JAN1974 STOCK3 78.620
4 01JAN1974 STOCK4 122.727
5 01JAN1974 STOCK5 140.875
6 01JAN1974 STOCK6 185.698
7 01JAN1974 STOCK7 162.728
8 01JAN1974 STOCK8 25.053
9 01JAN1974 STOCK9 73.913
10 01JAN1974 STOCK10 98.581
WORKING CODE
============
do over stocks;
stock_name=vname(stocks);
stock_value=stocks;
output;
FULL SOLUTION
=============
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
data have;
retain dte;
array stocks stock1-stock2000;
do dte='01JAN1974'd to '31DEC2004'd;
do over stocks;
stocks=200*uniform(5731);
end;
output;
end;
run;quit;
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
data want;
set have;
array stocks stock1-stock2000;
do over stocks;
stock_name=vname(stocks);
stock_value=stocks;
output;
end;
drop stock1-stock2000;
run;quit;
313 data want;
314 set have;
315 array stocks stock1-stock2000;
316 do over stocks;
317 stock_name=vname(stocks);
318 stock_value=stocks;
319 output;
320 end;
321 drop stock1-stock2000;
322 run;
NOTE: There were 11323 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 22646000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 10.58 seconds
cpu time 10.12 seconds
proc print data=want(obs=10) width=min;
format dte date9.;
run;quit;
Thanks a lot, its really helpful.
Are the date unique?
proc transpose data=have out=want ;
by code ;
run;
If your column headers in Excel really have colons in them then you might want to use the _LABEL_ variable instead the _NAME_ variable as the name of the stock since SAS might have had to convert something like 'ID:AAL' into 'ID_AAL' to get it to be a valid variable name.
I think you have already gotten answer from @Tom. If you want speed try ARRAY. array x{*} AAL AAP ABC ABD ABN ACA; do i=1 to dim(x); vname=vname(x{i}); value=x{i}; output; end;
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.
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.