topic Re: Transposing data with in multiple columns into three columns only in SAS Programming

Transposing data with in multiple columns into three columns only

MAC1430 — Sat, 08 Apr 2017 11:01:45 GMT

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

Re: Transposing data with in multiple columns into three columns only

rogerjdeangelis — Sat, 08 Apr 2017 12:55:15 GMT

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;

Re: Transposing data with in multiple columns into three columns only

Tom — Sat, 08 Apr 2017 13:18:59 GMT

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.

Re: Transposing data with in multiple columns into three columns only

Ksharp — Sat, 08 Apr 2017 14:44:46 GMT

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;

Re: Transposing data with in multiple columns into three columns only

MAC1430 — Sat, 08 Apr 2017 22:16:33 GMT

Thanks a lot, its really helpful.

Re: Transposing data with in multiple columns into three columns only

MAC1430 — Sat, 08 Apr 2017 22:20:03 GMT

Thanks a lot ksharp, you are always so helpful 🙂