topic Re: Easy way to Readi n an Excel File with a Column Per Year? in SAS Enterprise Guide

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

DavidPhillips2 — Wed, 31 Jan 2018 15:19:41 GMT

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.

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

FredrikE — Thu, 01 Feb 2018 07:54:44 GMT

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

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

DavidPhillips2 — Thu, 01 Feb 2018 14:43:29 GMT

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.

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

DavidPhillips2 — Fri, 02 Feb 2018 15:18:58 GMT

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/182635

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.

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

DavidPhillips2 — Fri, 02 Feb 2018 15:41:02 GMT

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.