I have the following code. I basically want to filter a table so that you are left with only the information relevant for the car colors in the table want_options. In dataset have_options, the car colors are listed in 3 different rows in column_a (i.e. blue on top of red on top of white). In the first data step, i have created want_options which combines these 3 rows into 1 row in column_b (new column), so it now appears as "blue", "red", "white" in 1 cell next to eachother. In the last data step, I want to filter dataset have_data by the selected colors from want_options (in this case blue, red, and white were chosen).
However, when I run the last data step, it highlights the comma in the line "blue", "red", "white" from the macro variable &car_color_options. and says both:
ERROR 388-185: Expecting an arithmetic operator.
ERROR 200-322: The symbol is not recognized and will be ignored.
Data want_options; Set have_options;
By car_brand; If first.car_brand then column_b = cats('"', column_a, '"'); Else column_b = (column_b, ' ,', '"', column_a, '"'); /* '"" is basically ' " ' in case you can't tell */ Run; %Let car_color = ""; Data _null_; Set want_options; If car_brand = "car_color" then call symput('car_color_options', column_b); Run; Data outcome; Set have_data; If &car_color_options. = "" then car_color_selected = 1; Else &car_color_options. ^= "" and car_color in (&car_color_options.) then car_color_selected = 1; Else &car_color_options. ^= "" and car_color not in (&car_color_options.) then car_color_selected = 0;
If car_color_selected = 1 then output; Run;
Anyone know how I can solve this? Thanks
Perhaps you want the code below? Comparing a list of strings to another string using = or ^= is not valid syntax.
Data outcome;
Set have_data;
If symget('car_color_options') = "" then car_color_selected = 1;
Else symget('car_color_options') ^= "" and car_color in (&car_color_options.) then car_color_selected = 1;
Else symget('car_color_options') ^= "" and car_color not in (&car_color_options.) then car_color_selected = 0;
If car_color_selected = 1 then output;
Run;
Well, you are missing a semi-colon ... and you probably have to put &car_color_options inside %quote(); but if that's not it, you have to show us the SASLOG so we can see WHERE these errors are occurring. Really, in this case showing us a SASLOG ought to be mandatory, we shouldn't have to ask to see where the error is.
Perhaps you want the code below? Comparing a list of strings to another string using = or ^= is not valid syntax.
Data outcome;
Set have_data;
If symget('car_color_options') = "" then car_color_selected = 1;
Else symget('car_color_options') ^= "" and car_color in (&car_color_options.) then car_color_selected = 1;
Else symget('car_color_options') ^= "" and car_color not in (&car_color_options.) then car_color_selected = 0;
If car_color_selected = 1 then output;
Run;
This seems like an easy problem for SQL, without needing any macro language:
proc sql;
create table want as select have_data.* where have_data.car_color in
(select distinct column_a from have_options);
quit;
My SQL isn't the strongest, so this may need a little tweaking.
Please post data exists in have.options, some test data of have.data and the full log of your run.
In your first step you have:
If first.column_a then ...
you cannot use first.column_a without defining: by colomn_a and dataset sorted by it.
It is not enough to post the errors, you have to post the full log in order to see the connection between the code and the error.
Run next code and check carefully the outputs:
%Let car_color = "";
Data want_options;
Set have_options;
By car_brand;
length column_b $30;
retain column_b;
If first.car_brand then column_b = cats('"', column_a, '"');
Else column_b = cats(column_b, ' ,', '"', column_a, '"');
if last.car_brand then output;
keep car_brand column_b;
Run;
data temp;
merge have_data
want_options;
by car_brand;
run;
Now you have color options per car_barnd. Which of them should be in a macro variable ?
Check carefully your logic and post full log of your run.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.