topic Re: tranpose wide to long in SAS Programming

tranpose wide to long

somebody — Wed, 06 May 2020 11:58:42 GMT

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;

Re: tranpose wide to long

Jagadishkatam — Wed, 06 May 2020 12:46:17 GMT

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;

Re: tranpose wide to long

Kurt_Bremser — Wed, 06 May 2020 12:40:53 GMT

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.