proc sql macro variable with commas

[SASAna]

Hi SAs users,

I am testing on the belwo query and getting errors - I have to build the macro variable with commas for charcters, and without commas for numbers.

Test query -

data test;
los_hours = 10;
var_name = Apple;
run;


%macro mac1(mname = , mac_value = , mac_var = );

Proc sql noprint;
select
   case when index(lowcase("&mname"),"los_") > 0
          or index(lowcase("&mname"),"_hrs_") > 0 then
     COMPRESS(MAC_VALUE)  into :&mac_var separated by ','
     else
        COMPRESS("'"||MAC_VALUE||"'") into :&mac_var separated by ','
    end
 from test;
    ;
    quit;

    %put =&mac_value;
%mend;

    %mac1(mname = los_hours , mac_value =10 );   - number testing

%mac1(mname = var_name , mac_value = Apple);   - character testing

[ballardw]

When you get errors you should include the code from the log with the errors. Best is to paste into a code box opened using the forum {I} menu icon to preserve formatting of the error diagnostics.

I will bet that your error has something to do with variable MAC_VALUE not found.

You need to reference the macro variable with the & to resolve MAC_VALUE  in the attempted code.

I am not sure what that is intended to do. I suspect a misuse of the compress function though.

[Tom]

Use the Insert Code icons on the forum editor menu to let you paste code into pop-up windows so that they are formatted properly.

1) What is your code trying to do?  Are you trying to find distinct values of a variable and generate a delimiter list of those values into a macro variable?

2) How is your macro supposed to know whether the user wants to query a numeric or character variable?  It looks like you are perhaps trying to figure that out based on the name?

3) You don't seem to be using the constant value you are passing in.  What is the purpose of that parameter?

[SASAna]

Thanks for the reply.

 I wanted to create this macro mac_var  with commas for character values &  no  commas for integer value  that is being passed in  MAC_VALUE.

I am getting syntax error on placement of  "into :&mac_var separated by ','  -" wrongly.

Proc sql noprint;
select
   case when index(lowcase("&mname"),"los_") > 0
          or index(lowcase("&mname"),"_hrs_") > 0 then
     COMPRESS(MAC_VALUE)                                    
     else
        COMPRESS("'"||MAC_VALUE||"'")
    end     as        into :&mac_var separated by ','  -
 from test;
    ;
    quit;

    %put =&mac_value;


 

[Tom]

Why do you want commas in one and not in the other?  That doesn't really make any sense.

I am having a hard time aligning your words with your posted code examples.

Let's try to make two lists using the SASHELP.CLASS dataset that has both numeric and character variables.

So this code will create two macro variables. One with values from a character variable and the other with values from a numeric variable. For one it will place commas between the values and for the other it will place spaces.

proc sql noprint;
select quote(trim(name))
     , age
  into :namelist separated by ','
     , :agelist separated by ' '
  from sashelp.class(obs=4)
;
quit;

1227  %put &=namelist;
NAMELIST="Alfred","Alice","Barbara","Carol"
1228  %put &=agelist;
AGELIST=14 13 13 14

Can you explain what you want to do?

[SASAna]

Thanks for the answers . I was looking for this code, i have passed my parameters and it excecuted very well. Thanks again.

proc sql noprint;
select case when index(lowcase(name),"los_") > 0   then trim(name)
      else quote(trim(name))
      end 
  into :namelist separated by ','
      from sashelp.class(obs=4)
;
quit;

%put &namelist;

[Tom]

---

@SASAna wrote:

Thanks for the answers . I was looking for this code, i have passed my parameters and it excecuted very well. Thanks again.

proc sql noprint;
select case when index(lowcase(name),"los_") > 0   then trim(name)
      else quote(trim(name))
      end 
  into :namelist separated by ','
      from sashelp.class(obs=4)
;
quit;

%put &namelist;

---

That still doesn't make much sense.  Why are you testing the value of the NAME variable to determine whether to add quotes around it?  Now if you were testing to see if the value contained commas that might make sense.

[SASAna]

Thanks for the tips, I have only one attribute which might have both Charcter or number being passed. so i was identifying it as if 'HRS' in the macro name then it is integer data else character data. Thanks much.

[ballardw]

---

@SASAna wrote:
Thanks for the tips, I have only one attribute which might have both Charcter or number being passed. so i was identifying it as if 'HRS' in the macro name then it is integer data else character data. Thanks much.

---

Example of what you have and the desired output with actual values to demonstrate makes things much easier on all concerned. Different industries and organizations will have jargon for some term, procedure, process or what have you that may mean something different or nothing to readers here. Concrete examples of the data and the desired results help avoid lots of words trying to describe something that is easier to see with an example.

[Tom]

I wanted to create this macro mac_var  with commas for character values &  no  commas for integer value  that is being passed in  MAC_VALUE.

Taken literally you are asking to take a value in MAC_VALUE.

%let MAC_VALUE=100 ;

And store it in MAC_VAR.

%let MAC_VAR=&MAC_VALUE;

Possibly you meant that MAC_VAR holds the NAME of the macro variable to create?

%let &MAC_VAR=&MAC_VALUE;