DATA Step, Macro, Functions and more

Link date value in a variable to the date on a column name

Reply
Contributor
Posts: 27

Link date value in a variable to the date on a column name

Does anyone have any thoughts on how I might accomplish the following:

I have a dataset with the following variables:

ID Assets0908 Assets0909 assets0910 Month1 month2 month3
1 $109 $200 $150 0909 0910 0911
2 $40 $20 $10 0908 0909 0910

I would like to sum variables Assets0909, Assets0910, Assets0910 based on the values in Month1-3. For example if month was 0908 then the month1_Assets would be the sum of Assets0908 and so on.

Please note that the values in month1-3 are character.. Is there a quick way to do this?
I would welcome any insights.
Valued Guide
Posts: 632

Re: Link date value in a variable to the date on a column name

I am sure that I am missing the heart of your question, but....
Since MEANS/SUMMARY is quite good at getting column totals why not get the totals (one obs) and then ask questions about that one row?
Super User
Posts: 9,671

Re: Link date value in a variable to the date on a column name

Is Assets0908 numeric ? if it is not ,can use input() function to convert.
if it is , try this Such As:

[pre]
length month1_Assets 4.;
retain month1_Assets;
.............
array month{3} $ ;
do i=1 to 3;
if month{i} eq '0908' then month1_Assets=sum(month1_Assets,Assets0908);
...............
[/pre]


Ksharp

Message was edited by: Ksharp
SAS Employee
Posts: 39

Re: Link date value in a variable to the date on a column name

Two ways come to mind for how to do this. One is to use an array of the asset variables, then use the value in the month variables to index into the array.

Another way to do this is to construct a variable name as a string, then call the VVALUEX function to get the value of that variable.

The array solution is probably the most efficient, yet you may have be crafty with defining the upper and lower bounds of the array. Both of these methods are shown at the bottom of this post.

A link to the doc for VVALUEX is here:

http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#/documentation/cdl/...

[pre]
/* Create sample dataset */
data info;
input ID (assets0908 assets0909 assets0910 assets0911) (:comma.) (month1 month2 month3) ($);
datalines;
1 $109 $200 $150 $190 0909 0910 0911
2 $40 $20 $10 $5 0908 0909 0910
;

/* Use month to index into ASSETS array */
data sums(keep=id asset_sum);
set info;
array assets[0908:0911] assets:;
array month
  • month:;

    asset_sum = 0;
    do j = 1 to dim(month);
    month_num = input(month, BEST12.);
    asset_sum + assets[month_num];
    end;
    run;

    /* Sum variables that are specified in the MONTH1-MONTH3 variables
    * (1) Construct variable name and place in ASSET_VARNAME
    * (2) Call VVALUEX and pass ASSET_VARNAME to get value of asset variable
    * (3) Convert character value returned by VVALUEX into a numeric value.
    * (4) Accumulate numeric value into ASSET_SUM
    */
    data sums2(keep=id asset_sum);
    set info;
    array month
  • month:;

    asset_sum = 0;
    do j = 1 to dim(month);
    asset_varname = 'assets' || left(month);
    asset_value = vvaluex(asset_varname);
    asset_sum + input(asset_value, best32.);
    end;
    run;

    proc print data=sums; run;
    [/pre]

    Output:
    [pre]
    Obs ID sum

    1 1 540
    2 2 70
    [/pre]
  • Ask a Question
    Discussion stats
    • 3 replies
    • 131 views
    • 0 likes
    • 4 in conversation