DATA Step, Macro, Functions and more

Transposing data with in multiple columns into three columns only

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

Transposing data with in multiple columns into three columns only

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


Accepted Solutions
Solution
‎04-08-2017 06:16 PM
Valued Guide
Posts: 505

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

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;



View solution in original post


All Replies
Solution
‎04-08-2017 06:16 PM
Valued Guide
Posts: 505

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

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;



Contributor
Posts: 63

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

Posted in reply to rogerjdeangelis

Thanks a lot, its really helpful.

Super User
Super User
Posts: 7,076

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

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.

 

 

Super User
Posts: 10,044

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

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;



Contributor
Posts: 63

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

Thanks a lot ksharp, you are always so helpful Smiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 262 views
  • 3 likes
  • 4 in conversation