Write a table depending on what kind of fruit and color and where it shall come from

[melhaf]

Hi!

I'm looking for a code that would be something like this in SAS usin proc SQL (or datastep):

%let FRUIT = banana;  /*choose between banana/apple/orange/plum*/
%let COLOR = yellow; /*choose between yellow/green/orange/purple*/

create table &fruit._&color._bowl as select distinct
*
from store.one as a 
full outer join as b on a.id=b.id;
quit;

I want to have a code similar like this above, but if the costumer (id) choose apple/green, it comes from store.two
and costumer choose orange from;: 

.... store.four.

How would that be done?

[PaigeMiller]

The first step in getting code with macro variables to work, is to create working code for one situation without macro variables, but with the value of &fruit and the value of &color hard coded. Please show us that working code first. (And please run this working SAS code to confirm that it does run without errors).

The code you have presented cannot possibly work, you have a number of syntax errors (including no PROC SQL; statement).

Also please explain how a programmer like you or me would know which libname to do use for the JOIN.

[melhaf]

I just made this up in my head. Hopefully people understand the purpose, but I can be more clear 

%let FRUIT = banana;  /*choose between banana/apple/orange/plum*/
%let COLOR = yellow; /*choose between yellow/green/orange/purple*/

proc sql;
create table &fruit._&color._bowl as select distinct
*
from store.one as a 
full outer join as b on a.id=b.id;
quit;

I don't even know IF this works.  But in English:

If want to have all the fruits that are yellow and are bananas;


create a table where it name is then bowl_banana_yellow;
and if we it comes from banana we have to go to store.one

and if we want bowl_kiwi_green
then the we have to go to store.two

[PaigeMiller]

Not clear to me.

I did ask earlier for code without macro variables that works. It seems as if you are saying this is totally made up example, and so you can't test it or even write working code. Can you make up an example that can be tested? Or use your real problem (with fake data if necessary because of confidentiality)? 

I also asked: "Also please explain how a programmer like you or me would know which libname to do use for the JOIN" but I don't see a GENERAL answer that can be used in a program. You have provided two examples (which is not a GENERAL answer) but there is no way to program this other than to hardcode something (%IF &color=yellow %then store.one; %ELSE ... ;)

[mkeintz]

I think I do understand your purpose.

But I also think I would like you to provide some working un-macroized code that would provide a good basis to offer macro solutions.

[Reeza]

---

@melhaf wrote:

I don't even know IF this works.  But in English:

If want to have all the fruits that are yellow and are bananas;


create a table where it name is then bowl_banana_yellow;
and if we it comes from banana we have to go to store.one

and if we want bowl_kiwi_green
then the we have to go to store.two

---

How do you know that banana maps to one and kiwi/green map to store two? Where is that logic stored? Is it hardcoded? You could make macro logic using %if/%then statements to drive this but there are probably other ways. It depends on how that lookup is determined.