DATA Step, Macro, Functions and more

sum dynamic numeric variable

Reply
Frequent Contributor
Posts: 115

sum dynamic numeric variable

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?


Super Contributor
Posts: 644

Re: sum dynamic numeric variable

Posted in reply to sunilreddy

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

Super Contributor
Posts: 644

Re: sum dynamic numeric variable

Posted in reply to RichardinOz

I recommend you store data in the following format

region_nmcu_nmyearquarternumber
REGIONX  201213
REGIONX  CUX201211
REGIONX  CUY201212
REGIONX  201224
REGIONX  CUX201222
REGIONX  CUY201222
REGIONX  201235
REGIONX  CUX201232
REGIONX  CUY201233
REGIONX  201246
REGIONX  CUX201240
REGIONX  CUY201240

With this structure you can do annual summaries by region_nm and cu_nm and transpose the results for your report.

Richard in Oz

Frequent Contributor
Posts: 115

Re: sum dynamic numeric variable

Posted in reply to 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

SAS Super FREQ
Posts: 8,866

Re: sum dynamic numeric variable

Posted in reply to sunilreddy

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

PROC Star
Posts: 7,474

Re: sum dynamic numeric variable

Posted in reply to sunilreddy

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)

Contributor
Posts: 67

Re: sum dynamic numeric variable

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:

QQ截图20121121105346.jpg

PROC Star
Posts: 7,474

Re: sum dynamic numeric variable

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;

Contributor
Posts: 67

Re: sum dynamic numeric variable

Thanks!

Ask a Question
Discussion stats
  • 8 replies
  • 357 views
  • 1 like
  • 5 in conversation