Assign value from another dataset via CALL EXECUTE

thargassner — Mon, 12 Sep 2016 11:53:18 GMT

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

Re: Assign value from another dataset via CALL EXECUTE

Kurt_Bremser — Mon, 12 Sep 2016 12:00:37 GMT

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.

Re: Assign value from another dataset via CALL EXECUTE

Ksharp — Tue, 13 Sep 2016 03:43:50 GMT

Where is your output ?
Try Hash Table .

topic Re: Assign value from another dataset via CALL EXECUTE in SAS Programming

Assign value from another dataset via CALL EXECUTE

Re: Assign value from another dataset via CALL EXECUTE

Re: Assign value from another dataset via CALL EXECUTE