01-31-2018 10:19 AM
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.
02-01-2018 02:54 AM
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
02-01-2018 09:42 AM - edited 02-01-2018 09:43 AM
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.
02-02-2018 10:14 AM - edited 02-02-2018 10:18 AM
Is there a way to do a dynamic column rename?
To determine the new name I am looking at using the below logic.
create table TestImportCols as
select * from dictionary.columns
/*where memname = 'TEST IMPORT'*/
where memname = Column Categories';
/*full list of column names*/
data TestImportCols2 (keep=colname); set TestImportCols;
length colname $200.;
if label ne '' then colname = label;
else colname = name;
/*Then I run statements to clean up the text of colname, which can map back to the orginal column name.*/
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 " "
proc datasets library=work;
I do not understand the logic when dealing with labels due to over 32 characters.
02-02-2018 10:41 AM
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.