BookmarkSubscribeRSS Feed
SAS09
Calcite | Level 5
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.
3 REPLIES 3
ArtC
Rhodochrosite | Level 12
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?
Ksharp
Super User
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
JasonS_SAS
SAS Employee
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]
  • sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    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.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 3 replies
    • 825 views
    • 0 likes
    • 4 in conversation