Hi,
I've columns 2011Y,2012Q1,2012Q2,2012Q3,2012Q4 these dynamic column will chnage time to time.
So i want to add two new columns 2012Y (sum of 2012Q1,2012Q2,2012Q3,2012Q4) and 2011/2012YOY(diff of 2012Y and 2011Y).
Is there any way to add the columns by keeping position number instead of column name?
The answer depends on whether you want a solution that works for 2012 and can be adapted for subsequent years, or an automated solution tha twill automatically add in year totals as it detects new columns for the quarters.
The former is simple. You can use a datastep which will allow you to reuse the table name. You will need to set the option that will recognise name literals (the non standard column names).
Data results ;
Retain region_nm cu_nm '2011Y'N '2012Q1'N '2012Q2'N '2012Q3'N '2012Q4'N '2012Y'N '2011/2012YOY'N '2013Q1'N '2013Q2'N;
Set results ;
'2012Y'N = Sum ('2012Q1'N, '2012Q2'N, '2012Q3'N, '2012Q4'N) ;
'2011/2012YOY'N = '2012Y'N - '2011Y'N ;
run ;
I will suggest a better way of storing your data to remove the need to add columns in your database.
Richard in Oz
I recommend you store data in the following format
region_nm | cu_nm | year | quarter | number |
REGIONX | 2012 | 1 | 3 | |
REGIONX | CUX | 2012 | 1 | 1 |
REGIONX | CUY | 2012 | 1 | 2 |
REGIONX | 2012 | 2 | 4 | |
REGIONX | CUX | 2012 | 2 | 2 |
REGIONX | CUY | 2012 | 2 | 2 |
REGIONX | 2012 | 3 | 5 | |
REGIONX | CUX | 2012 | 3 | 2 |
REGIONX | CUY | 2012 | 3 | 3 |
REGIONX | 2012 | 4 | 6 | |
REGIONX | CUX | 2012 | 4 | 0 |
REGIONX | CUY | 2012 | 4 | 0 |
With this structure you can do annual summaries by region_nm and cu_nm and transpose the results for your report.
Richard in Oz
These column names will chnage for subsequent years, but column position will not chnage in existing table for subsequent years also(names will change. ex: for in Jan 2013 year, i have 2012Y, 2013Q1 2013Q2 2013Q3 2013Q4)
How to use these calculations
Hi:
It looks like this question is related to the other posting (https://communities.sas.com/message/145004#145004 ) you made in the ODS Graphics forum. It's still not clear to me whether you want a graph, a dataset, or a report.
cynthia
One way to achieve the desired result is to create a macro that does the job. e.g.:
/* Create some test data */
data have;
input '2011Y'N '2012Q1'N '2012Q2'N '2012Q3'N '2012Q4'N;
cards;
6 1 2 3 4
;
%macro addyear(fname,year);
data &fname.;
set &fname.;
"&year.Y"N=Sum ("&year.Q1"N, "&year.Q2"N, "&year.Q3"N, "&year.Q4"N) ;
"%eval(&year.-1)/&year.YOY"N = "&year.Y"N - "%eval(&year.-1)Y"N ;
run;
%mend addyear;
%addyear(have,2012)
Hi,Tabachneck
I have a problem, when I execute your code:
data have;
input '2011Y'n '2012Q1'N '2012Q2'N '2012Q3'N '2012Q4'N;
cards;
6 1 2 3 4
;
run;
There is an ERROR:
You had specified those variable names (i.e., variable names that start with a number) in your original post. You can only do that if you set the system option:
options validvarname=any;
Thanks!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: