Hi TiffanyAching,
If have done something similar before. Note that you only can change the label name of a column not the column name.
Also note that code below is not optimized, but it show you all steps needed and will do the job.
Month code:
data Month_value ;
length Month_value $3 ;
input Month_value $3.;
datalines;
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
;
Query Builder with prompt
Add Month value in tab Select Data
Create new prompt called 'Month_value'
Select option 'Use prompt value throughout project'
Add all months as values
Create new program 'Set header'
%MACRO setHeaders(Month_value=);
%GLOBAL Month0 Month1 Month2 Month3 Month4 Month5;
%if &Month_value=Jun %then %do;
%let Month0=Jul;
%let Month1=Aug;
%let Month2=Sep;
%let Month3=Oct;
%let Month4=Nov;
%let Month5=Dec;
%end;
%else %if &Month_value=Jul %then %do;
%let Month0=Aug;
%let Month1=Sep;
%let Month2=Oct;
%let Month3=Nov;
%let Month4=Dec;
%let Month5=Jan;
%end;
%MEND setHeaders;
%setHeaders(Month_value=&Month_value);
Note that you add all the months. In the example I have added only 2
Create new program 'Data'
This will contain your data.
data work.input;
input Associate $10. ReportMonth $4. Month0 3. Month1 3. Month2 3. Month3 3. Month4 3. Month5 3.;
cards;
Joe Bloggs Jul 01 02 03 04 05 00
Joe Bloggs Jun 02 03 04 05 00 07
Joe Bloggs May 03 04 05 00 07 01
Mary Sue Jul 01 41 23 01 06 01
Mary Sue Jun 14 12 31 06 12 01
Mary Sue May 12 31 06 12 01 03
;
run;
And last, create a program called 'Want'
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_INPUT_0007 AS
SELECT t1.Associate,
t1.ReportMonth,
t1.Month0,
t1.Month1,
t1.Month2,
t1.Month3,
t1.Month4,
t1.Month5
FROM WORK.INPUT t1
WHERE %_eg_WhereParam( t1.ReportMonth, Month_value, CONTAINS, TYPE=S, IS_EXPLICIT=0 );
QUIT;
proc datasets lib=work nolist;
modify QUERY_FOR_INPUT_0007;
label Month0=&Month0;
label Month1=&Month1;
label Month2=&Month2;
label Month3=&Month3;
label Month4=&Month4;
label Month5=&Month5;
run; quit;
If you run all from the top, you see that the labels of the month colums are changing with each month selection you have done.
If you add a listdata for example, you will see the correct column names.
Hope this will be usefull in some kind of way.
Daniël
... View more