Deleting columns based on their relative position in a dataset

[Huub]

Hi,

So i have this dataset in Excel with 20 columns where the column headers consist of different dates. I am trying to write soms SAS EG code that removes the second column of this dataset and inserts a new column at the end of the dataset. I have managed to convert the excel dataset tot a SAS dataset which i have called WORK.Historie_DV_Nood. I have also managed to isolate the column name of the column that I want to remove using the code 

/* column list extracted */
proc contents data=WORK.Historie_DV_Nood out=column_nm varnum;
run;

proc sort data=column_nm out=col_nm_sorted(keep = libname memname name);
by varnum;
run;

data filtered_col;
set col_nm_sorted(firstobs=&no_of_col obs=&no_of_col);
run;

proc sql noprint;
select name  into :_col_name
from filtered_col;
quit;

%put &_col_name;

This gives the output 

52 %put &_col_name;
44305

However when I try to drop this column with this code:

data want;
set WORK.Historie_DV_Nood (drop= "&_col_name");
run;

it gives the error 

ERROR 214-322: Variable name "44305 " is not valid.

ERROR 23-7: Invalid value for the DROP option.

I have tried to fix this using option validvarname = any; and by chaning the quotation marks but that does not work. Does anyone know what is going wrong?

[yabwon · Accepted solution]

Try:

(drop= "&_col_name"N );

B.

[Huub]

This worked. Thanks.

[Kurt_Bremser]

Excel does not have datasets, it has spreadsheets. SAS has datasets.

So what do you have? I guess that you need to import a spreadsheet from Excel into SAS, and that spreadsheet has dates as column names. This will usually cause problems, as the import process uses the raw, unformatted values as names, and these are counts of days starting with 1899-12-31 as day 1.

Now, it is always a bad idea to put data (dates) in structure (variable names).

Your first step after import should be a transpose of the wide structure to a long one, which makes it then much easier to convert the raw numbers to SAS dates with proper format, drop observations with unwanted dates, and add observations for new dates.

If you need the wide structure again for presentation means (e.g. export to Excel), you can achieve that easily with PROC REPORT.

If handling the non-valid variable names from the initial import causes problems (SAS names must not start with a digit), use

options validvarname=any;

for the initial manipulation/conversion, and reset it to

options validvarname=v7;

immediately afterwards. One simply does not want to have to use name literals all the time.

For more detailed help, attach the Excel file in question to your next post, so we can give you ideas how to deal with it.