BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
2 REPLIES 2
RichardH_sas
SAS Employee
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.
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 837 views
  • 0 likes
  • 3 in conversation