BookmarkSubscribeRSS Feed
TiffanyAching
Calcite | Level 5

Hello everyone,

 

I get the feeling that what I'm about to ask simply might not be possible.

 

I have a dataset with the columns Associate, ReportMonth, Month0, Month1, Month2, Month3, Month4 and Month5. In VA, my list table is designed so that ReportMonth variable is assigned to a drop down box which filters the table, a single ReportMonth value is required. Dummy data below:

AssociateReportMonthMonth0Month1Month2Month3Month4Month5
Joe BloggsJul-18123450
Joe BloggsJun-18234507
Joe BloggsMay-18345071
Mary SueJul-18014123161
Mary SueJun-181412316121
Mary SueMay-18123161213

 

Month0-Month5 all relate to the same metric and together gives a 6 month history from the given report month backwards.

So, in July-18 Month0=July18, Month1=Jun18, Month2=May18, Month3=Apr18 etc.

In Jun-18 Month0=Jun18, Month1=May18, Month2=Apr18, Month3=Mar18 etc.

 

My list table only allows one ReportMonth to be viewed at once, so this means that the Month0-5 columns all mean the same for everything in the list table, but of course when a different report month is selected, they would mean something else. I'm hoping to be able to overwrite the Month0-Month5, with the actual calendar months they refer to.

 

Anyone done anything similar? As I say, I fear it's a longshot!

 

1 REPLY 1
DJongman
Obsidian | Level 7

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.

 

overall_project.jpg

 

 

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

 

Prompt_values.jpg

 

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.Listdata.jpg

 

 

Hope this will be usefull in some kind of way.

 

Daniël

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!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 2028 views
  • 0 likes
  • 2 in conversation