topic Re: Labeling and formatting in SQL in SAS Programming

Labeling and formatting in SQL

Mruizv — Sun, 24 Apr 2022 18:40:57 GMT



PROC SQL;

Title "Within";
	CREATE TABLE program as
	select var1,
		var2,
		var3 as First,
		avg(var4) as mean label='Average',
		var5-CALCULATED mean as within label='Within'

	from programab


where var6=1
group by var7
order by var3
;

QUIT;

Trying to compress the results for mean and within- compress option error says I need a character variable, is the placement critical like in the where/groupby/order?

tried using compress(var,'0',) at the beginning of statement, before "var3", before "as" and before "label" statement -some options say variable is not contributing the table. Even tried creating a second variable as the compressed one (trying to get rid of zeroes after decimal point)

The following columns were not found in the contributing tables: mean.

compress('mean','0',)as mean2

Label is not changing anything again; is placement critical? (need to change titles)

Re: Labeling and formatting in SQL

PaigeMiller — Sun, 24 Apr 2022 18:44:03 GMT

When there are errors in the log, please SHOW US the log. We need to see the ENTIRE log for this PROC SQL, do not show us error messages detached from the rest of the log. Please copy the log as text and paste it into the window that appears when you click on the </> icon

From now on, please do this EVERY time you have errors in the log.

Re: Labeling and formatting in SQL

Mruizv — Sun, 24 Apr 2022 18:55:56 GMT

This particular one does not have any errors, the one show is with a different iteration, but will take into consideration for next time. thanks!

Re: Labeling and formatting in SQL

PaigeMiller — Sun, 24 Apr 2022 18:57:53 GMT

Then what did you mean when you said this?

compress option error says I need a character variable

Re: Labeling and formatting in SQL

Mruizv — Sun, 24 Apr 2022 19:09:33 GMT

the code I supplied does not show a compress statement, so the error is not part of that code.
When I run the iteration with the compress statement as I said prior is when I get the error.
"
The following columns were not found in the contributing tables: mean.
compress('mean','0',)as mean2
"

I have tried multiple iterations with compress unsuccessfully those being my 2 main errors.
If you have any clue how to compress both mean and within to eliminate trailing zeros I will test it out. As of now my code has nothing to do so

Re: Labeling and formatting in SQL

PaigeMiller — Sun, 24 Apr 2022 19:51:02 GMT

@Mruizv wrote:
the code I supplied does not show a compress statement, so the error is not part of that code.
When I run the iteration with the compress statement as I said prior is when I get the error.
"
The following columns were not found in the contributing tables: mean.
compress('mean','0',)as mean2
"

SHOW US THE ENTIRE LOG for this PROC SQL

Also, don't show us code that works and then ask about code that doesn't work which you haven't shown us. I find that very confusing.

Re: Labeling and formatting in SQL

Tom — Sun, 24 Apr 2022 20:10:29 GMT

@Mruizv wrote:
the code I supplied does not show a compress statement, so the error is not part of that code.
When I run the iteration with the compress statement as I said prior is when I get the error.
"
The following columns were not found in the contributing tables: mean.
compress('mean','0',)as mean2
"

I have tried multiple iterations with compress unsuccessfully those being my 2 main errors.
If you have any clue how to compress both mean and within to eliminate trailing zeros I will test it out. As of now my code has nothing to do so

This is why it is important (and easier for you) to just copy the lines from the SAS log and post that.

1) The error message is that there is no variable named mean.

2) The code you showed does not even try to reference a variable named mean. Just the string 'mean'.

3) It makes no sense to use COMPRESS() with a MEAN because a mean value should be a number, not a character string.

Please explain what you are trying to do.

Re: Labeling and formatting in SQL

Mruizv — Sun, 24 Apr 2022 20:16:43 GMT

Hello Tom,
I am trying to get rid of trailing zeros after the decimal point. In OP I asked if it is placement sensitive as I tried 3 different locations all with same outcome hence why I removed it before asking the question, what i have so far and "working" is what I posted.
Also in OP asked if label is placement sensitive as it is not changing the label in the output.

Re: Labeling and formatting in SQL

Tom — Sun, 24 Apr 2022 20:28:21 GMT

You cannot remove digits from a number. The number is the number it is.

Are you asking how to display the number in different way? You could use a format without any positions for decimal places.

Are you asking to change the number to a different number? Do you want to round the number? round down to the nearest integer? round up to the next highest integer?

Are you asking to convert the number to a character string?

How did they look at the data? Only some procedures will display the label instead of the name of the variable. You can also override the label attached to the variable by including a LABEL statement in the step that is displaying the values.

Re: Labeling and formatting in SQL

Mruizv — Sun, 24 Apr 2022 20:34:44 GMT

yes, just display in a different way i have 154.300 that i would like it to be 154.3 or a 3.7000 that i would like a 3.7 hence I was trying compress as I had used it before in similar cases deleting the '0', there are other numbers that come from the output that use all the decimal places and should remain the same, no rounding no deleting. only the zeros.

With Label I have also tried removing it from the SQL and adding a PROC PRINT adding the label to no avail.
I currently have a label inside the SQL as you can see in the calculations of var4 and var5 turning into mean and within, they still show as mean and within and not as the attached label

Re: Labeling and formatting in SQL

PaigeMiller — Sun, 24 Apr 2022 21:14:45 GMT

Do you realize that if the number was 304.300, you would wind up removing the zeros and then you would have 34.3?

Maybe (still not sure what you are doing) you want to format the number by limiting it to one digit to the right of the decimal. Is that what you want to do? Then use a format such as 10.1

Example:

avg(var4) as mean label='Average' format=10.1

Is that what you are trying to do?

This will actually ROUND the result to the nearest tenth. Is that what you are trying to do?

Re: Labeling and formatting in SQL

Mruizv — Mon, 25 Apr 2022 00:04:25 GMT

For some reason the QUERY SQL was not outputting, I changed my settings and now it is outputting correctly, with the proper titles and without the trailing zeros

This is how the code ended, I deleted the previously used PROC PRINT as now I got the output from the SQL.

PROC SQL;

Title "Within Treatment Group Differences on The First Visit";
	CREATE TABLE diffweight as
	select patient,
		gender,
		v_date as First_visit,
		avg(weight) as mean label='Group_Average',
		weight-CALCULATED mean as within label='Within_Group_Diff'

	from stat.clinical

where visit=1
group by group
order by v_date

;
QUIT;

20 21 PROC SQL; 22 23 Title "Within Treatment Group Differences on The First Visit"; 24 CREATE TABLE diffweight as 25 select patient, 26 gender, 27 v_date as First_visit, 28 avg(weight) as mean label='Group_Average', 29 weight-CALCULATED mean as within label='Within_Group_Diff' 30 31 from stat.clinical 32 33 where visit=1 34 group by group 35 order by v_date 36 37 ; NOTE: The query requires remerging summary statistics back with the original data. NOTE: Table WORK.DIFFWEIGHT created, with 24 rows and 5 columns. 38 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.00 seconds

This is what I was getting with the print

This is what I was looking for

Re: Labeling and formatting in SQL

SASKiwi — Mon, 25 Apr 2022 00:55:07 GMT

What does "getting with the print" mean? Were you using PROC PRINT? If you are then add the LABEL option to print labels instead of variable names:

proc print data = diffweight label;
run;

Re: Labeling and formatting in SQL

Tom — Mon, 25 Apr 2022 01:00:30 GMT

Of course the SQL step did not print anything, you did not ask it to. But don't use SQL to print. SAS has many much better tools to print.

If you want flexible number of decimal places then use BEST format. It will pick the "best" way to display each individual number.

For more help show the actual code you ran to produce the print out. The SQL code is not it.

PS the way the numbers are printing in your first example is much better than the way you have requested them. How is a human looking at those numbers with decimal points in random places supposed to be able to compare the values across rows?

Re: Labeling and formatting in SQL

PaigeMiller — Mon, 25 Apr 2022 13:10:27 GMT

@Mruizv wrote:

This is what I was looking for

Hello, @Mruizv

May I give some advice that will help you, and help us help you, in the future? Please be direct, state the desired results clearly, every single time. You didn't do that until about the 12th message of this thread, which is shown above. By showing us the desired output in your first message, you would have gotten a very quick answer. But instead, we didn't know what you wanted until you showed us this table, but we knew you were trying things we didn't understand, and we didn't know where you were trying to go. From now on, make sure you show us (and explain) the desired results in your first message.