Proc SQL Select Into Clause with Formats

[ifb10]

Hello everyone, 

I am having some issues with value formatting using macro variables with formats generated by proc sql; 

proc sql noprint;
select sum(A) format = COMMAX22.2
       into: A_SUM
       from table;
quit;

data test;
    attrib Value length=8 format=COMMAX22.2;
    Value = &A_SUM.;
run;

Die resultierenden Werte "Value" ist entweder nicht formatiert, oder es kommt zu einem Error. Lösche ich die Formatangabe im Proc SQL step dann funktioniert alles einwandfrei.

Daher die Frage: Wie wird es mit Formaten gehandhabt, wenn die Werte über SQL kommen. Ist es hier überhaupt sinnvoll, dem Makro variablen ein Format zu verpassen?

Viele Grüße

[PaigeMiller · Accepted solution]

You could help us all out greatly by showing us the value of macro variable &a_sum. And whenever you get errors in the log, it helps greatly to show us the log (not just the error messages, but the ENTIRE log for the DATA step or PROC that has the problem, emphasis on ENTIRE log); in fact this should not be considered optional, it should be considered mandatory to show us the ENTIRE log for the DATA step or PROC with the error.

Nevertheless, if this value is >=1000, you will most likely find the value has a dot in it, and if there are value in decimals you will also see a comma, and so you will see something like this for the value of &a_sum

1.184,40

So, in your next block of code, you have this line of code

    Value = &A_SUM.;

so when the macro variable resolves, this becomes

    Value = 1.184,40;

which is not valid syntax. So, homework assignment for you, @ifb10 , what is wrong with this syntax? What would you have to do to fix it (without macros, just type in a command that would work here)? Fill in the blank with valid SAS syntax: 

Value = _____________;

[ifb10]

@PaigeMiller 

You are right, the value was larger than 1000. In this case I should write something along the lines of :

value = input("1.184,40",COMMAX22.2);

Thank you, that definitely solves the problem. I however still do not grasp the concept of giving a macro variable a format. I guess it is for the log and makes debugging simpler.  Yet is that all to it?

Best

ifb10

[PaigeMiller]

There is no need to format a macro variable and then un-format it to use it. Just don't format it in the first place. This works properly.

proc sql noprint;
    select sum(height) into: A_SUM
    from sashelp.class;
quit;
%put &=a_sum;

data test;
    value=&a_sum;
run;

If you want the value in data set TEST to have COMMAX format, apply the format there, not in SQL.

But the issue I asked about in the statement value=&a_sum; is the real problem. What is wrong with your original code using the COMMAX. format, after the macro variable &a_sum is resolved and produces this line:

    Value = 1.184,40;

If you are going to use macro variables, when they resolve (and are replaced with their value), the result MUST BE valid working SAS code that does what you want. You must grasp this point if you are going to use macro variables.

So ... why doesn't this work? Why is this not valid working SAS code?

[ifb10]

Thanks,

my assumption is that sas uses . as a delimiter with numeric values. Hence the , in the assignment statement would lead to an error because the symbol is not recognized. 

 Value = 1.184,40;

  My solution would be to prevent this with an input statement providing the right numeric format: 

value = input("1.184,40",COMMAX22.2);

[PaigeMiller]

Numbers in SAS code cannot have commas in them. When you use macro variables, the rules of SAS still must be followed when the macro variable is resolved. So if the macro variable, when resolved, puts a comma in a number (or makes any other violation of the rules of SAS), you will get an error. You MUST grasp this point when you try to use macro variables.

The solution, as I said, is to not apply the format to the macro variable and then everything works fine. Then you get

value = 1184.4;

which will work properly.

The solution is NOT as you have programmed it: assign a format to the macro variable and then "un-format" it with the INPUT command to use it.

[ifb10]

@PaigeMiller 

I understand that it is easier and much cleaner not to apply formats for the macro variable (As stated in my initial statement). I asked this question, because I am working on a system where previous developers used this method (assign a format to the macro variable and then "un-format"), so I was trying to find any benefits in using it. From what I understood you see none. 

[PaigeMiller]

---

@ifb10 wrote:

@PaigeMiller 

I understand that it is easier and much cleaner not to apply formats for the macro variable (As stated in my initial statement). I asked this question, because I am working on a system where previous developers used this method (assign a format to the macro variable and then "un-format"), so I was trying to find any benefits in using it. From what I understood you see none. 

---

This is not what I was saying, but I agree that there are no advantages because it produces code that does not work (without additional programming)

I was trying to explain WHY this method doesn't work, because you will need to understand this if you are going to use macro variables. I was trying to explain how you can avoid these problems.

[Tom]

---

@ifb10 wrote:

@PaigeMiller 

I understand that it is easier and much cleaner not to apply formats for the macro variable (As stated in my initial statement). I asked this question, because I am working on a system where previous developers used this method (assign a format to the macro variable and then "un-format"), so I was trying to find any benefits in using it. From what I understood you see none. 

---

The only reasonable reason to apply a format like COMMAX when generating a macro variable is because you want the value displayed in that style for some TEXT report you are making.  Perhaps in a TITLE or FOOTNOTE statement.

But generating a formatted value and then having to use a informat to convert the value back into something useful seems silly.

Sometimes you do need to apply a format to prevent PROC SQL from using its default BEST8. format to avoid loss of significant digits.  Or when the variable being selected already has a format attached to it and you don't want that format applied.  But your example does not have a format attached since the value you were extracting was the result of function call and not just the value of a variable.

[Tom]

Why put stuff into the macro variable that you then need to remove?
Or for that matter why put data into a macro variable at all?  Leave data in datasets.

proc sql noprint;
create table A_SUM as
  select sum(A) as A_SUM
  from table
;
quit;

data test;
    attrib Value length=8 format=COMMAX22.2;
    if _n_=1 then set a_sum ;
    Value = A_SUM;
    drop a_sum;
run;

[Tom]

Don't put stuff into the macro variable you don't want there.

proc sql noprint;
select sum(A) format = F22.2
       into :A_SUM trimmed
       from table;
quit;

data test;
    attrib Value length=8 format=COMMAX22.2;
    Value = &A_SUM.;
run;