sum dynamic numeric variable

[sunilreddy]

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?

[RichardinOz]

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

[RichardinOz]

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

[sunilreddy]

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

[Cynthia_sas]

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

[art297]

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)

[Slash]

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:

[art297]

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;

[Slash]

Thanks！