DATA Step, Macro, Functions and more

How to stack multiple Columns below each other Using SAS SQL (Interleave or Concatenate)???

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to stack multiple Columns below each other Using SAS SQL (Interleave or Concatenate)???

[ Edited ]

Have table:

Name
ASTRAZENECA PLCBARCLAYS PLCBNP PARIBAS
10/22/20030.3187706480.3656556390.374383913
10/23/20030.3195767660.3656961050.373288326
10/24/20030.3151516550.365296990.370542635
10/27/20030.3151354410.3659016660.37086854
10/28/20030.3142884050.3658393020.371058966
10/29/20030.3142822270.36409550.371062983
10/30/20030.3142807450.3636819040.367175412
10/31/20030.315021930.3634538140.366553058

 

Want table:

DateNameSTD
10/22/2003ASTRAZENECA PLC0.318771
10/23/2003ASTRAZENECA PLC0.319577
10/24/2003ASTRAZENECA PLC0.315152
10/27/2003ASTRAZENECA PLC0.315135
10/28/2003ASTRAZENECA PLC0.314288
10/29/2003ASTRAZENECA PLC0.314282
10/30/2003ASTRAZENECA PLC0.314281
10/31/2003ASTRAZENECA PLC0.315022
10/22/2003BARCLAYS PLC0.365656
10/23/2003BARCLAYS PLC0.365696
10/24/2003BARCLAYS PLC0.365297
10/27/2003BARCLAYS PLC0.365902
10/28/2003BARCLAYS PLC0.365839
10/29/2003BARCLAYS PLC0.364095
10/30/2003BARCLAYS PLC0.363682
10/31/2003BARCLAYS PLC0.363454
10/22/2003BNP PARIBAS0.374384
10/23/2003ASTRAZENECA PLC0.373288
10/24/2003ASTRAZENECA PLC0.370543
10/27/2003ASTRAZENECA PLC0.370869
10/28/2003ASTRAZENECA PLC0.371059
10/29/2003ASTRAZENECA PLC0.371063
10/30/2003ASTRAZENECA PLC0.367175
10/31/2003ASTRAZENECA PLC0.366553

Accepted Solutions
Solution
2 weeks ago
PROC Star
Posts: 765

Re: How to stack multiple Columns below each other Using SAS SQL (Interleave or Concatenate)???

Posted in reply to Agent1592

Like this?

 

data have;
input Name:mmddyy10. ASTRAZENECA_PLC BARCLAYS_PLC BNP_PARIBAS;
format Name mmddyy10.;
datalines;
10/22/2003 0.318770648 0.365655639 0.374383913 
10/23/2003 0.319576766 0.365696105 0.373288326 
10/24/2003 0.315151655 0.36529699 0.370542635 
10/27/2003 0.315135441 0.365901666 0.37086854 
10/28/2003 0.314288405 0.365839302 0.371058966 
10/29/2003 0.314282227 0.3640955 0.371062983 
10/30/2003 0.314280745 0.363681904 0.367175412 
10/31/2003 0.31502193 0.363453814 0.366553058 
;

proc transpose data=have out=want(rename=(name=Date _NAME_=Name COL1=STD));
   by Name;
run;

View solution in original post


All Replies
Solution
2 weeks ago
PROC Star
Posts: 765

Re: How to stack multiple Columns below each other Using SAS SQL (Interleave or Concatenate)???

Posted in reply to Agent1592

Like this?

 

data have;
input Name:mmddyy10. ASTRAZENECA_PLC BARCLAYS_PLC BNP_PARIBAS;
format Name mmddyy10.;
datalines;
10/22/2003 0.318770648 0.365655639 0.374383913 
10/23/2003 0.319576766 0.365696105 0.373288326 
10/24/2003 0.315151655 0.36529699 0.370542635 
10/27/2003 0.315135441 0.365901666 0.37086854 
10/28/2003 0.314288405 0.365839302 0.371058966 
10/29/2003 0.314282227 0.3640955 0.371062983 
10/30/2003 0.314280745 0.363681904 0.367175412 
10/31/2003 0.31502193 0.363453814 0.366553058 
;

proc transpose data=have out=want(rename=(name=Date _NAME_=Name COL1=STD));
   by Name;
run;
Super User
Posts: 7,854

Re: How to stack multiple Columns below each other Using SAS Data Step or SQL (Interleave or Concate

Posted in reply to Agent1592

Look at my answer to your other thread, there you see how it is solved with proc transpose.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 765

Re: How to stack multiple Columns below each other Using SAS SQL (Interleave or Concatenate)???

Posted in reply to Agent1592

Btw, is it a requirement to use SQL? Why not PROC TRANSPOSE?

Super User
Posts: 7,854

Re: How to stack multiple Columns below each other Using SAS SQL (Interleave or Concatenate)???

Posted in reply to Agent1592

SQL is a database language, and databases are not built in this way (data in structure). You could beat SQL into doing it with macro-preprocessing, but that would be stupid, considering that you just need 4 lines of proc transpose to achieve it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 120 views
  • 1 like
  • 3 in conversation