BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nowak22
Fluorite | Level 6

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

View solution in original post

11 REPLIES 11
ballardw
Super User

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.

bobpep212
Quartz | Level 8

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;
nowak22
Fluorite | Level 6
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.
Tom
Super User Tom
Super User

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.

 

nowak22
Fluorite | Level 6

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

nowak22
Fluorite | Level 6
Yep, thank you very much both of you.
With "sep" I am having an error, without it, it works fine.
Tom
Super User Tom
Super User

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.

Kurt_Bremser
Super User

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)?

nowak22
Fluorite | Level 6
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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 11 replies
  • 1055 views
  • 2 likes
  • 5 in conversation