Assign value from another dataset via CALL EXECUTE

[thargassner]

Hi fellow SAS developers,

I am trying to calculate a new column in a dataset based on values of another dataset.

DS1 contains following columns:
NUM category
NUM number_a
NUM number_b

DS2 contains following columns:
NUM year
NUM columns with the name of the category in DS1, meaning the value of the column 'category' in DS1 is the column name in DS2 (numerical column name, e.g. 1.3).

DS1
-------------------------------
category | number_a | number_b
-------------------------------
1.0      | 9        | 5
1.5      | 14       | 3
-------------------------------

DS2
---------
year | 1.0 | 1.5 | 2.5
----------------------
3    | 0.1 | 0.2 | 0.3
4    | 0.2 | 0.3 | 0.4
5    | 0.8 | 0.4 | 0.7
11   | 0.5 | 0.6 | 0.2
----------------------

First approach was to write the code directly in the CALL EXECUTE statement.

DATA DS1;
    SET DS1;
    number_diff = number_a - number_b;
    value_a = CALL EXECUTE("PROC SQL; SELECT 'category'n FROM DS2 WHERE year = " || number_diff || ";  QUIT;");
    value_b = CALL EXECUTE("PROC SQL; SELECT 'category'n FROM DS2 WHERE year = " || number_b || "; QUIT;");
    new_column = (value_b - value_a) / (1 - value_a);
RUN;

A second approach was calling a macro out of the data step with call execute.

%MACRO GET_VALUE(category=, year=);
    PROC SQL;
        SELECT '&category.'n
        /*INTO :pd_value*/
        FROM DS2
        WHERE year = &year.
        ;
    QUIT;
%MEND GET_VALUE;

DATA DS1;
    SET DS1;
    number_diff = number_a - number_b;
    value_a = CALL EXECUTE('%GET_VALUE(category=' || category || ' , year='|| number_diff ||');');
    value_b = CALL EXECUTE('%GET_VALUE(category=' || category || ' , year='|| number_b ||');');
RUN;

I also tried to select into a variable in the macro.

%MACRO GET_VALUE(category=, year=);
    PROC SQL;
        SELECT '&category.'n
        INTO :return_value
        FROM DS2
        WHERE year = &year.
        ;
    QUIT;
%MEND GET_VALUE;

DATA DS1;
    SET DS1;
    number_diff = number_a - number_b;
    return_value = CALL EXECUTE('%GET_VALUE(category=' || category || ' , year=' || number_diff || ');');
    value_a = return_value;
    return_value = CALL EXECUTE('%GET_VALUE(category=' ||category || ' , year=' || number_b || ');');
    value_b = return_value;
RUN;

Thanks for all hints and solutions.

Best regards,

Thomas

[Kurt_Bremser]

First of all, numbers are not valid SAS names and can therefore not be used to name variables.

Second, putting data (values) into structure (variable names) is usually a BAD IDEA and better avoided.

So you're better off putting your values into a separate variable that can then be used in a BY statement to group your further processing.

It would be helpful to state the intention behind all this.

[Ksharp]

Where is your output ?
Try Hash Table .