BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MAC1430
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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

5 REPLIES 5
rogerjdeangelis
Barite | Level 11
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;



MAC1430
Pyrite | Level 9

Thanks a lot, its really helpful.

Tom
Super User Tom
Super User

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.

 

 

Ksharp
Super User
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;



MAC1430
Pyrite | Level 9
Thanks a lot ksharp, you are always so helpful 🙂

sas-innovate-2024.png

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.

 

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
  • 5 replies
  • 933 views
  • 3 likes
  • 4 in conversation