How to create new columns from existing columns keeping part of the column name

MellyJ13 — Sat, 27 Feb 2021 22:44:07 GMT

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!

Re: How to create new columns from existing columns keeping part of the column name

Shmuel — Sun, 28 Feb 2021 01:08:55 GMT

Next code is tested:

data have;
  input Date mmddyy10. Holding_AAPL Holding_MSFT ;
cards;
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
; run;
proc sql noprint;
  select name into :list separated by ' '
  from dictionary.columns 
  where libname='WORK' and memname = 'HAVE' and name ne 'DATE'
;quit;
data _null_;
     length newvars $1000; /* adapt to max length need */
     varin = "&list";
     nvars = countw(varin);
     do i=1 to nvars;
        newvars = catx(' ',newvars,' DAY_'||scan(varin,i));
     end;
     call symput('nvars',strip(put(nvars,4.)));
     call symput('newvars',strip(newvars));
run;
%put NVARS=&nvars;
%put &list;
%put &newvars; 

data want;
 set have;
     retain &newvars;
     array vin {&nvars} &list;
     array vot {&nvars} &newvars;
     output;
     do i=1 to &nvars;
        vot(i) = vin(i); 
     end;
     drop i;
run;

topic Re: How to create new columns from existing columns keeping part of the column name in New SAS User

How to create new columns from existing columns keeping part of the column name

Re: How to create new columns from existing columns keeping part of the column name