Easy way to Readi n an Excel File with a Column Per Year?

[DavidPhillips2]

Is there a simple way to cleanly read in a file like the below description with SAS: 

The Excel file has 2000 columns.  The data set contains five years of data where columns are split out by year.

The column names are like:

Grand total (EF2012B_RV  All Students total  All age categories total)

Grand total (EF2013B_RV  All Students total  All age categories total)

E.g. 2012 and 2013 are years.

Years can be missing from the dataset.  So some columns for the years are missing.

The length of the Excel column names are sometimes long 200 characters.

Ideally I’d like to extract the year value and merge the columns and create a table with 400 columns.

[FredrikE]

Can't give you real code whitout sample data, but I think you can do it using the transpose task.

You should be able to transpose the years to rows 🙂

//Fredrik

[DavidPhillips2]

Sample data is attached.

The goal is to merge columns together, so each column contains five values per school.  The year needs to be extracted into its own column.  When there is a column name over a particular length I would like to manually name it in a step.  Sufix text like _RV needs to be removed.

[DavidPhillips2]

Is there a way to do a dynamic column rename?

To determine the new name I am looking at using the below logic.

proc sql;

     create table TestImportCols as

     select * from dictionary.columns

     /*where memname = 'TEST IMPORT'*/

     where memname = Column Categories';

     ;

quit;

/*full list of column names*/

data TestImportCols2 (keep=colname); set TestImportCols;

     length colname $200.;

     if label ne '' then colname = label;

     else colname = name;

run;

/*Then I run statements to clean up the text of colname, which can map back to the orginal column name.*/

https://communities.sas.com/t5/SAS-Data-Management/How-to-dynamically-rename-multiple-variables/td-p...

This thread has:

"Do your long form variables meet SAS variable naming restrictions, i.e. 32 characters or less, no spaces, and starts with character?

If they don't you'll want to set them as labels instead.

If so I usually do something like the following:

proc sql noprint;

select catx("=", old_name, new_name)

     into :rename_list separated by " "

from key_dataset;

quit;

proc datasets library=work;

modify my_data;

rename &rename_list;

run;"

I do not understand the logic when dealing with labels due to over 32 characters.

[DavidPhillips2]

It looks like I can simply reference the original column name rather than the label when doing the rename statement since both the label and original column name will be in the table.