BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ifb10
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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 = _____________;

 

--
Paige Miller

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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 = _____________;

 

--
Paige Miller
ifb10
Obsidian | Level 7

@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
Diamond | Level 26

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?

--
Paige Miller
ifb10
Obsidian | Level 7

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
Diamond | Level 26

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.

--
Paige Miller
ifb10
Obsidian | Level 7

@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
Diamond | Level 26

@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.

--
Paige Miller
Tom
Super User Tom
Super User

@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
Super User Tom
Super User

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 10 replies
  • 1301 views
  • 2 likes
  • 3 in conversation