Transform dataset using a pivot

[rcolipi]

I'm hoping you can help me with some code to change observations to variables, I have data with a structure like so:

date Index apple msft
02-01-2022 575 656 595
03-01-2022 544 682 564
04-01-2022 598 641 579
05-01-2022 718 770 589
06-01-2022 553 528 659
07-01-2022 701 500 639
08-01-2022 560 727 603
09-01-2022 733 718 774
10-01-2022 626 663 651
11-01-2022 740 715 679
12-01-2022 614 512 533
13-01-2022 517 720 634
… … … …

and I would like to generate a dataset with the following structure:

date stock value
02-01-2022 Index 575
03-01-2022 Index 544
04-01-2022 Index 598
05-01-2022 Index 718
06-01-2022 Index 553
… … …
02-01-2022 apple 656
03-01-2022 apple 682
04-01-2022 apple 641
05-01-2022 apple 770
06-01-2022 apple 528
… … …
02-01-2022 msft 595
03-01-2022 msft 564
04-01-2022 msft 579
05-01-2022 msft 589
06-01-2022 msft 659
… … …

help me?

[mkeintz · Accepted solution]

If you really only have a few stocks, then this will do what you need, without transposing and sorting:.

data want;
  set have (drop=apple msft  rename=(index=value) in=in_index)
      have (drop=index msft  rename=(apple=value) in=in_apple)
      have (drop=index apple rename=(msft=value)  in=in_msft) ;
  if in_index=1 then stock='Index'; else
  if in_apple=1 then stock='apple'; else
  if in_msft=1  then stock='msft'; 
run;

If you have a lot more stocks, then something like will still avoid sorting, but take a lot more memory:

data want (keep=date stock value);
  if 0 then set have;
  length stock $8;
  array stocks {*} index--msft;

  declare hash h(dataset:'have',ordered:'a');
    h.definekey('date');
    h.definedata(all:'Y');
    h.definedone();
  declare hiter hi ('h');

  do s=1 to dim(stocks);
    stock=vname(stocks{s});
    do rc=hi.first() by 0 until (hi.next()^=0);
      value=stocks{s};
      output;
    end;
  end;
run;

[rcolipi]

Indeed, there are hundreds of stocks. It works perfectly, thank you very much