Hi there
I have data on multiple companies that I need to lag into new columns but I want to keep the suffix of the original column name in the new column name.
The data looks something like this (but it has about 2000 more columns with the same structure holding_"company ticker and about 1000 more rows":
Data Set Test
Date Holding_AAPL Holding_MSFT
04/05/2020 300 250
04/06/2020 305 251
04/07/2020 310 248
04/08/2020 307 249
04/09/2020 306 250
04/10/2020 312 250
I need it like this for all the companies
Date Holding_AAPL DAY_Holding_AAPL Holding_MSFT DAY_Holding_MSFT
04/05/2020 300 600
04/06/2020 305 300 580 600
04/07/2020 310 305 581 580
04/08/2020 307 310 592 581
04/09/2020 306 307 595 592
04/10/2020 312 306 599 595
this is what I have been trying to automate the process:
data TEST;
set TEST;
array HOLDING HOLDING_AAPL -- HOLDING_MSFT;
do over HOLDING;
day[holding] = lag1(holding)
end;
end;
run;
But I get an error that day is not an array. Any help to get the new column names to have "Day_'old column name'" would be much appreciated!