Automating the variables according to reporting month

Reply
Contributor
Posts: 44

Automating the variables according to reporting month

This one is slightly tricky

 

There are variables in the source data table say sample1 which is like this

 

Spend_number_1179

Spend_number_1180

Spend_number_1181

Spend_number_1182

Spend_number_1183

.....

....

Spend_number_1211

 

These variables are renamed in another table say sample2 like this which has monthly spending

 

Spend_number_1211 is renamed in the label as "Monthly Spend for Jul 2017"

Spend_number_1210 is renamed in the label as "Monthly Spend for Jun 2017"

Spend_number_1209 is renamed in the label as "Monthly Spend for May 2017"

Spend_number_1208 is renamed in the label as "Monthly Spend for Apr 2017"

Spend_number_1207 is renamed in the label as "Monthly Spend for Mar 2017"

 

Suppose if I automated for every reporting month. I take reporting month as July 30, 2017 it should take variable till Monthly Spend for Jul 2017 (this is just label) the actual variable name is spend_number_1211. Even using intnx function which I used earlier for automation... how can i automate the variable selection till reporting month... say example jul31, 2017 or jun30,2017..the variables starts from jan 2014 but spend_number_1211 needs some connection with dates... Can I use proc format or other way.. please help of how can i use date reporting connection to those variables thanks!

 

 

Super User
Posts: 6,982

Re: Automating the variables according to reporting month

What was the date for Spend_number_1179?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,433

Re: Automating the variables according to reporting month

This is a commoon question from people who do not knwo the difference between data manipulation and display, generally caused by using Excel.  You data, from what you have posted, is a date and a monthly spend, thus to model this for programming would look something like:
TIMEPOINT   SPEND   LAB

01JUL2017     xxx.xx     Monthly Spend for JUL2017

01JUN2017    xxx.xx      Monthly Spend for JUN2017

...

 

This data structure allows you to process the data - including dates and such like, with a limited structure, i.e. you don't need to know all the variables names like you do in yours, or rename etc.  You can then simply filter out various timepoints, do calculations across etc.  This is the programming or data manipulation side of things and does not need to be in the same format as the output structure.

 

The output structure can simply be achieved once all programming needs are done, be achieved by transposing this data up into the report format:

proc transpose data=... out=want;
  by ...;
  var spend;
  idlabel lab;
run;
Contributor
Posts: 44

Re: Automating the variables according to reporting month

RW9 that is not the case. There are 6 tables with different fields and this table has transaction details only with no time point just month spending. It is joined with other tables. It has only monthly spending with numbers as ext like spend_number_1211 which is for jul 2017 month. I think 1179 is jan 2014. It does not mention month name just numbers as extension renamed as monthyear in label. How can i automate these month fields according to reporting month which will select in prompt.
Super User
Super User
Posts: 7,433

Re: Automating the variables according to reporting month

Having no idea of your data, or why the various given parts which you have provided map to certain things, I haven't a clue.  Maybe get someone in to properly model your data into a useable format?

 

Super User
Posts: 6,982

Re: Automating the variables according to reporting month

As @RW9 already said, the proper way to go about it is to first transpose this to a reasonable format where data (dates) is not kept in structure (variable names). Once that is achieved, converting the sequential numbers to a date is something like this:

data have; /* assumed table after transposing */
input period :$20. amount;
cards;
Spend_number_1179 10
Spend_number_1180 5
Spend_number_1181 30
Spend_number_1182 24
Spend_number_1183 15
;
run;

data want;
set have;
period = scan(period,3,'_');
per_seq = input(period,4.) - 1179;
period = put(intnx('month','01jan2014'd,per_seq,'b'),yymmn6.);
drop per_seq;
run;

You can see how simple the task becomes as soon as you have intelligent data structures.

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,410

Re: Automating the variables according to reporting month

Am I correct - your tables are:

 

1) Data table with variables like: spend_number_#### where #### represents a 4 digits number

2) Labels table - for each entry in the data table there is a description like: "Monthly Spend for month_mame year"

3) You have a program that creates a report.

    The program is running monthly. Each month uses its own data variable.

     You want to automate definition of variable name and variable label given as arguments to your program.

 

To automate it, you have to create your variables names and assign them to macro variable according to running month.

Then use those macro variables in your program to create the report.

 

If you need help in coding, you have to post:

1)  code to create test data table with input lines for test. (3 months will be enough).

2)  code to create test lablel table with lines for same month as above.

3) Try create a code to merge the two tables.

    Use call symput to assign the data to macro variables.

    Continue with code to create  a simple report.

4) Tell us what issues do you have. Where do you need help.

 

Contributor
Posts: 44

Re: Automating the variables according to reporting month

Shmuel you mentioned the correct approach. Sorry folks i cant change the source data. I am working for a client company which is one of the world leading companies in finance but this is how they keep their structure. This being a small team within this company.

I will create macro variable and assign it against the variable names. If there is issue i will let you know
Super User
Posts: 6,982

Re: Automating the variables according to reporting month


sameer112217 wrote:
Shmuel you mentioned the correct approach. Sorry folks i cant change the source data. I am working for a client company which is one of the world leading companies in finance but this is how they keep their structure. This being a small team within this company.



That's a strawman argument, if I ever saw one. Tables for Business Intelligence will ALWAYS have another structure than those used in the data sources. You do not CHANGE the source data, but you PREPARE data for analysis on import into SAS.

When data has to be inserted back into the original system, you can then recreate the orignal structure, but letting original structure get in the way of proper working with SAS is a waste of time and therefore suboptimal, to be polite.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

Re: Automating the variables according to reporting month

[ Edited ]

Hello Everyone,

 

Appologise just had discussion with one of my client. We discussed how to create automation.

 

 

 

We decided to do this. It will be easy for anyone to provide code

 

We will create a seperate table with additional field name month. Table will be named as sample

 

variable name will be spend and month

 

Spend_number_179  JAN2016

Spend_number_180  FEB2016

Spend_number_181  MAR2016

Spend_number_182  APR2016

Spend_number_184  MAY2016

Spend_number_185  JUN2016

Spend_number_186 JUL2016

Spend_number_187 AUG2016

Spend_number_188 SEP2016

Spend_number_189 OCT2016

Spend_number_190 NOV2016

Spend_number_191 DEC2016

Spend_number_192 JAN2017

Spend_number_193 FEB2017

Spend_number_194 MAR2017

Spend_number_195 APR2017

Spend_number_196 MAY2017

Spend_number_197 JUN2017

Spend_number_198 JUL2017

Spend_number_199 AUG 2017

 

I want the variable from Spend_number_179  till reporting month spend variable  Reporting month will be JUL 2017 today so it will be till Spend_number_198

 

I want a code where it will take only variable values of spend from jan 2016 to reporting month. And later the label should be suppose if it is Spend_number_196 then its label should be MAY2017

 

Spend_number_197 then its label should be JUN2017.

 

This table I will use to fetch spend variable values as variable name  till reporting month when I will use join with other table

 

 

 

Thanks!

 

 

Trusted Advisor
Posts: 1,410

Re: Automating the variables according to reporting month

I asked for:

If you need help in coding, you have to post:
1)  code to create test data table with input lines for test. (3 months will be enough).
2)  code to create test lablel table with lines for same month as above.
3) Try create a code to merge the two tables.
    Use call symput to assign the data to macro variables.
    Continue with code to create  a simple report.
4) Tell us what issues do you have. Where do you need help.

but you gave posted the alternative to point 1 and 2 only.

 

In this case, start with next code:

data sample;
   input spend $17. monthx $8.;
   monthn = input('01'||compress(monthx), date9.);
   format monthn date9.;
datalines;
Spend_number_179  JAN2016
Spend_number_180  FEB2016
Spend_number_181  MAR2016
Spend_number_182  APR2016
Spend_number_184  MAY2016
Spend_number_185  JUN2016
Spend_number_186 JUL2016
Spend_number_187 AUG2016
Spend_number_188 SEP2016
Spend_number_189 OCT2016
Spend_number_190 NOV2016
Spend_number_191 DEC2016
Spend_number_192 JAN2017
Spend_number_193 FEB2017
Spend_number_194 MAR2017
Spend_number_195 APR2017
Spend_number_196 MAY2017
Spend_number_197 JUN2017
Spend_number_198 JUL2017
Spend_number_199 AUG 2017
; run;

%let from_month = jan2016;
%let upto_month = jul2017;
data _NULL_;
 set sample end=eof;
     length keep_vars $400;
     retain keep_vars;
     from_mm = input('01'||compress("&from_month"), date9.);
     upto_mm = input('01'||compress("&upto_month"), date9.);
     months = intck('month',from_mm,upto_mm);
     if eof then call symput('keepvars',trim(keep_vars));
     if from_mm le monthn le upto_mm;
     keep_vars = catx(' ',keep_vars, spend);
run;

%put KEEP = &keepvars;
 
data for_rep;
 set data_table (keep = &keepvars <+ other variables needed> );
      .... any more code preparing for report ...
run;
 

I can't help more until you supply info for points 3 and 4.

Ask a Question
Discussion stats
  • 10 replies
  • 132 views
  • 2 likes
  • 4 in conversation