topic Re: Conditions in a column as a macro variable to identify every row in a dataset. in SAS Programming

Conditions in a column as a macro variable to identify every row in a dataset.

nowak22 — Mon, 06 Apr 2020 22:03:06 GMT

Hey.

Let's consider the table 'Cars' and table 'Dictionary'.

Table dictionary has two variables, first: ID of a row, second: condition which determines what ID has a particular row.

For instance, where a record:

Type='SUV' and Horsepower > 300 and Cylinders > 6 THEN ID=TYPE_A

Now, using var_condition and var_id I would like to flag every record of a dataset by an 'ID' and stack them in a new table. I tried simple macro %if &var_condition then new_column = &ID, but I am not even close. How can I approach this task?

data cars;
set sashelp.cars;
run;

data Dictionary;
   input ID : $6. Condition & $211.;
datalines;
TYPE_A upcase(Type) IN('SUV', 'SPORTS', 'WAGON') and upcase(Origin) = 'ASIA' AND Horsepower > 150
TYPE_B Horsepower >= 220 and upcase(Origin)='EUROPE'
TYPE_C Type='SUV' and Horsepower > 300 and Cylinders > 6
;
proc sql noprint;
select ID into :var_id seprated by ' '
from Dictionary;
proc sql noprint;
select Condition into :var_condition seprated by ' '
from Dictionary;

Re: Conditions in a column as a macro variable to identify every row in a dataset.

ballardw — Tue, 07 Apr 2020 20:27:58 GMT

Did you read your log?

Dictionary is a special word for proc sql and when you do not specify the library that such as with WORK.Dictionary then proc sql expects a name of one of the dictionary tables that SAS maintains such as Dictionary.Tables, Dictionary.columns or others.

So a a minimum you need code similar to:

proc sql noprint;
select ID into :var_id separated by ' '
from work.Dictionary;
quit;
proc sql noprint;
select Condition into :var_condition separated by ' '
from Work.Dictionary;
quit;

Note correction in "separated" as well.

You don't show anything about how you actually attempted to use the macro variables.

Since %IF would be applied in the compile phase of a data step it is very likely that you compared something not available to the data step.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

bobpep212 — Mon, 06 Apr 2020 22:34:28 GMT

I'm guessing you are wanting to use the types (ID) to pass into some sort of where clause where the condition is then applied. If I'm guessing right, then you'd take your dataset with your ID & condition text, then use data _null_ with call symputx to create macro variables for each type, that would hold the conditional text string. Then you use the macro variable to refer to the condition in later where statements or SQL clauses. Is this what you're trying to do?

data cars;
set sashelp.cars;
run;

data Dictionary;
   input ID : $6. Condition & $211.;
datalines;
TYPE_A upcase(Type) IN('SUV', 'SPORTS', 'WAGON') and upcase(Origin) = 'ASIA' AND Horsepower > 150
TYPE_B Horsepower >= 220 and upcase(Origin)='EUROPE'
TYPE_C Type='SUV' and Horsepower > 300 and Cylinders > 6
;
data _null_;
set dictionary;
call symputx(id,condition);
run;

data want;
set sashelp.cars;
where &TYPE_A.;
run;

Re: Conditions in a column as a macro variable to identify every row in a dataset.

Tom — Tue, 07 Apr 2020 12:43:08 GMT

You definitely don't want to generate a macro %IF statement if you want to apply the condition to actual data.

Step 1 is to write the SAS code you want to generate.

data want;
  set cars;
  length id $6 ;
  if  upcase(Type) IN('SUV', 'SPORTS', 'WAGON') and upcase(Origin) = 'ASIA'
      AND Horsepower > 150 then id="TYPE_A";
  else if Horsepower >= 220 and upcase(Origin)='EUROPE' then id="TYPE_B ";
  else if Type='SUV' and Horsepower > 300 and Cylinders > 6 then id="TYPE_C";
run;

Once you get that working then you can try to figure out how to generate it from the data you have.

filename code temp;
data _null_;
  set dictionary;
  file code;
  if _n_ > 1 then put ' else' @ ;
  put ' if ' condition 'then id=' id :$quote. ';' ;
run;
data want;
  set cards;
  length id $6;
%include code / source2;
run;

Updated: to remove spurious SEP variable reference in PUT statement.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

Kurt_Bremser — Tue, 07 Apr 2020 07:12:09 GMT

Maxim 18: Separate Your Names.

"Dictionary" is a "keyword" in SQL and should therefore not be used for names of objects (datasets, variables).

Which of your conditions should take precedence if an observation meets more than one condition (e.g. an SUV from Asia or Europe with 8 cylinders and 350 horsepower)?

Re: Conditions in a column as a macro variable to identify every row in a dataset.

nowak22 — Tue, 07 Apr 2020 07:40:26 GMT

Thank you for your replay.
That is something that I am hoping to achieve.
However, if the record fulfills the condition, then I would like to mark it using the ID column. If it does not, then I would like to check for second condition and mark it by ID, until it goes through every condition.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

nowak22 — Tue, 07 Apr 2020 07:58:00 GMT

Thank you for your replay, you are certainly right about naming datasets.

About the precedence:
Check the first condition (first row from column Condition) if true, then mark it as first row from column ID, if not, proceed to the second row, until end of the column Condition, until the end of the conditions.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

Kurt_Bremser — Tue, 07 Apr 2020 08:07:28 GMT

Then @Tom's code provides the solution.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

nowak22 — Tue, 07 Apr 2020 08:54:16 GMT

Hey, thank your for your replay.

Does this code compiles without an error for you?

data cars;
set sashelp.cars;
run;

data dictionary;
   input ID : $6. Condition & $211.;
datalines;
TYPE_A upcase(Type) IN('SUV', 'SPORTS', 'WAGON') and upcase(Origin) = 'ASIA' AND Horsepower > 150
TYPE_B Horsepower >= 220 and upcase(Origin)='EUROPE'
TYPE_C Type='SUV' and Horsepower > 300 and Cylinders > 6
;

filename code temp;
data _null_;
  set dictionary;
  file code;
  if _n_ > 1 then put 'else' @ ;
  put sep ' if ' condition 'then id=' id :$quote. ';' ;
run;

data want;
  set cars;
  length id $6;
%include code / source2;
run;

I am ha

Re: Conditions in a column as a macro variable to identify every row in a dataset.

Kurt_Bremser — Tue, 07 Apr 2020 09:04:53 GMT

What should "sep" do in this statement?

put sep ' if ' condition 'then id=' id :$quote. ';' ;

Your code seems to work well without it.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

nowak22 — Tue, 07 Apr 2020 09:44:19 GMT

Yep, thank you very much both of you.
With "sep" I am having an error, without it, it works fine.

Re: Conditions in a column as a macro variable to identify every row in a dataset.

Tom — Tue, 07 Apr 2020 12:40:22 GMT

The SEP should be removed. It was left over from a different coding pattern for how to handle the conditional generation of the ELSE keyword.