I tried to create a new data set that keeps only a few columns from one huge data set. The new data set will have a different number of columns and I want to save them their names in excel because there will be frequent changes and I don't want to make changes to the code.
I tried to store the column names in excel - (It contains one sheet in the first column of which all the column names are stored which I want to save as the names are listed in rows). This is my code which works but is not correct. With this code - I import all names from excel. But when I add it in a columns_to_keep variable and in the next step I try to save it, the new dataset keeps only the last row name. How can I keep all columns? Example In Excel, I add 10 column names, the new data set should also keep these 10 names for me, not 1.
libname excelin excel "C:\Users\Xxxxxx\Xxxxx\WPS\your_excel_file.xlsx";
data ColumnNames;
set EXCELIN.'Sheet1$'n;
call symputx('columns_to_keep', column);
run;
data newdata ;
keep &columns_to_keep;
set ACCOUNTS;
run;
Assuming you have worksheet with a single column like this:
VARNAME
ID
AGE
SEX
DOB
Then you can use PROC SQL and the SELECT ... INTO ... syntax to create a single macro variable with the list ID AGE SEX DOB.
proc sql noprint;
select nliteral(trim(varname)) into :columns_to_keep separated by ' '
from EXCELIN.'Sheet1$'n
;
quit;