I have a dataset of fund holdings as attached. It is currently in a wide format. I would like to change it to a long format where there is one date column and one number_of_share column. Attached is
I tried using the following code to transpose but it does not work. It says No variables to tranpose. I tried with a different dataset and it works
proc transpose data=sample2 out=sample_long;
by fund_ticker constituent_ticker name Detail_Holding_Type ;
run;
You have invalid SAS names in that CSV (numeric), which my University Edition dutifully changed to names with leading underlines on import.
Then this code worked:
proc sort data=import;
by fund_ticker constituent_ticker name Detail_Holding_Type;
run;
proc transpose data=import out=sample_long;
by fund_ticker constituent_ticker name Detail_Holding_Type;
var _:;
run;
Note that there are a lot of ambiguous observations in the imported dataset (multiples for the same keys), which leads to more output columns than expected.
please try the below code
proc transpose data=have;
by fund_ticker constituent_ticker name detail_holdeing_type;
var 20180102 20180103 20180104 20180105 20180108;
run;
You have invalid SAS names in that CSV (numeric), which my University Edition dutifully changed to names with leading underlines on import.
Then this code worked:
proc sort data=import;
by fund_ticker constituent_ticker name Detail_Holding_Type;
run;
proc transpose data=import out=sample_long;
by fund_ticker constituent_ticker name Detail_Holding_Type;
var _:;
run;
Note that there are a lot of ambiguous observations in the imported dataset (multiples for the same keys), which leads to more output columns than expected.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: