topic Re: sum dynamic numeric variable in SAS Programming

sum dynamic numeric variable

sunilreddy — Sun, 18 Nov 2012 11:39:39 GMT

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?

Re: sum dynamic numeric variable

RichardinOz — Sun, 18 Nov 2012 12:26:14 GMT

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

Re: sum dynamic numeric variable

sunilreddy — Sun, 18 Nov 2012 12:35:53 GMT

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

Re: sum dynamic numeric variable

RichardinOz — Sun, 18 Nov 2012 12:46:49 GMT

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

Re: sum dynamic numeric variable

art297 — Sun, 18 Nov 2012 14:37:47 GMT

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)

Re: sum dynamic numeric variable

Cynthia_sas — Mon, 19 Nov 2012 04:25:56 GMT

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

Re: sum dynamic numeric variable

Slash — Wed, 21 Nov 2012 02:54:50 GMT

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:

Re: sum dynamic numeric variable

art297 — Wed, 21 Nov 2012 03:28:33 GMT

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;

Re: sum dynamic numeric variable

Slash — Wed, 21 Nov 2012 04:05:07 GMT

Thanks！