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?
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;
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;
Indeed, there are hundreds of stocks. It works perfectly, thank you very much
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: