topic Column header modification in SAS Enterprise Guide

Column header modification

deleted_user — Tue, 12 Jan 2010 16:13:04 GMT

I am building a project in SAS EG 4.2 and I am wondering if it is possible to changes the name of the columns with out doing it manually. I have 24 months of data each in its own column 12 months of previous data the current month and 11 months of future data. I would like to be able to have the month and year as the column title and each month have them change automatically. Here are the current column headers in EG.

-12 -11 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 Current Month 1 2 3 4 5 6 7 8 9 10 11

I would like the current month column title to be January 2010 (or current month) and then subtract or add from the current month and to get the appropriate month and year for each individual months relating to the current month. I will be running this project each month so the current month will always be changing.

Any help would be appreciated

Re: Column header modification

RichardH_sas — Wed, 13 Jan 2010 14:28:58 GMT

Here are some preliminary thoughts:

1. Writing code would probably be the slickest solution. I'd have to play around to figure out what the best way to do this would be. Two techniques that come to mind are using dictionary.columns with PROC SQL or using a VNAME function within some DATA step code. Neither choice would be appropriate to give to a novice programmer.

2. If you wanted to avoid writing code, you can do this in EG, but it's going to be a little messy. A stack columns task could get all the column names into a single column. You could then use a computed column expression in the query builder to manipulate the values (-12 to 11) and create valid names. Check out a PUT function and a MONYY format (I'm ommitting some details here!). Lastly you could use a split columns task on the data to spread things across the original 24 columns.

If you could handle some of this when the data's being created, that would be even better. In other words, if there's already some SAS code that create a data set with -12 through 11, maybe you could tweak that code to give you what you want.

Re: Column header modification

Peter_C — Mon, 25 Jan 2010 15:58:03 GMT

AirNav
don't know if you're still interested, but since I've something similar, thought I might offer the code below.
Assumptions:
1 : the -12 through 11 are variable labels (to be replaced with "month year" dates)
2 : for demonstration purposes, I assume your data is in a table named in macro variable &_data_1
3 : the header of that data set can be updated (a fairly high level of privilege)
* create data with the required columns for testing ;[pre] %let _data_1 = your_data ;
data &_data_1 ; retain m1-m24 . ;
label m1='-12'
m2='-11'
m3='-10'
m4='-9'
m5='-8'
m6='-7'
m7='-6'
m8='-5'
m9='-4'
m10='-3'
m11='-2'
m12='-1'
m13= 'Current Month'
m14= '1'
m15= '2'
m16= '3'
m17= '4'
m18= '5'
m19= '6'
m20= '7'
m21= '8'
m22= '9'
m23= '10'
m24= '11'
;
run;

proc contents data= &_data_1 noprint out= _data_ ;
run ;
data _null_ ;
if _n_ = 1 then do ;
set &syslast ;
call execute( 'proc datasets nolist lib= '!! libname ) ;
call execute( '; modify ' !! memname !! '; label ' ) ;
end ;
set &syslast end= finished ;
oLabN = input( label, ?? 12. ) ;
if label='Current Month' then lab= put( today(), nlDateYM. );
else if ( -12 LE oLabN LE 11 ) then
lab = put( intnx( 'month', today(), oLabN ), nlDateYM. );
call execute( name !! '= ' !! lab ) ;
if finished ;
call execute( '; run ; quit ;' ) ;
run ;[/pre] null