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!
What was the date for Spend_number_1179?
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;
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?
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.
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.
@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.
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.