PROC SQL does not work with multiple variables

[PamG]

I would like to report counts with percentages in the next column after  the counts for several sepecific conditions.  The easiest way for me is to do with PROC SQL.  However the third line of code given the sample code does not work. I want to report percentages after counts, not the other way around.  It seems the order of variables matter when using INTO in SAS.  Is there a way around it without using multiple SELECT statements?

   
data one;
  do x=-2 to 2 by 0.01;
  y=(x-2)*(x-1.5)*(x-1)*(x-.5)*x*(x+.5)*(x+1)*(x+1.5)*(x+2);
  output;
  end;
  run;

  proc sql;
  select count(x),sum(x) into :a  from one; **works;
 select sum(x) into :a,count(x)  from one; **does not work;

[data_null__]

I think you want this.  I would suggest data set is more useful.  You don't want to have to manage all that data in macro variables.

data one;
  do x=-2 to 2 by 0.01;
     y=(x-2)*(x-1.5)*(x-1)*(x-.5)*x*(x+.5)*(x+1)*(x+1.5)*(x+2);
     output;
     end;
  run;

proc sql;
   select count(x),sum(x) into :countx, :sumx  from one; **works;
   quit;
%put NOTE: &=countx &=sumx;

proc summary data=one;
   output out=summary n(x)=countx sum(x)=sumx;
   run;
proc print;
   run;

65         proc sql;
66            select count(x),sum(x) into :countx, :sumx  from one;
66       !                                                          **works;
67            quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

68         %put NOTE: &=countx &=sumx;
NOTE: COUNTX=     401 SUMX=6.63E-13

[Kurt_Bremser]

You say you want a percentage. Percentage of what vs. what?

Note that counts and sums let you calculate averages, not percentages.

Please explain what you're after,  e.g. by posting a "want" dataset derived from your example dataset one.

[PamG]

This is a sample data. What I am trying to get at is that the order in which the variables are entered in PROC SQL when one of them is entered as INTO matters. Is there a way to get COUNT before SUM and assign COUNT to a macro variable in the same PROC SQL code?

[Kurt_Bremser]

---

@PamG wrote: Is there a way to get COUNT before SUM and assign COUNT to a macro variable in the same PROC SQL code?

---

@data_null__ has already answered that. But I have a strong inkling that your final goal can be achieved without the macro detour.

[Tom · Accepted solution]

You already have the answer but note that I find that if you structure your program in this way the matching of the macro variable name with the value it will receive is more obvious.

proc sql noprint;
select min(age)
     , max(age)
  into :min_age trimmed
     , :max_age trimmed
  from sashelp.class
;
quit;
%put MAX Age = "&max_age";
%put MIN Age = "&min_age";

[PamG]

Is there a way to output these results to output window on SAS EG?  the %put statment  goes to log and using the following code, you cannot format the text.  I wonder if I should start another thread.

DATA _NULL_;
	FILE PRINT;
	line1="MAX Age = &max_age, Min Age=&min_age";
	PUT #1 line1 10-25 ;
	FILE LOG;
RUN;

[Tom]

If you just want to produce a report then I wouldn't bother to use macro variables.

PROC SQL will print to the output window (if you don't include the NOPRINT option that I used to suppress it when creating macro variables).  Or use PROC SUMMARY or some other tool to make a dataset with the values you want and then use PROC PRINT or PROC REPORT to write your report.

It would be good to start a new thread and include more details about the overall goal you are trying to achieve if you need more help.

[PamG]

Thanks @Tom. I started a new thread.

[PaigeMiller]

---

@PamG wrote:

This is a sample data. What I am trying to get at is that the order in which the variables are entered in PROC SQL when one of them is entered as INTO matters. Is there a way to get COUNT before SUM and assign COUNT to a macro variable in the same PROC SQL code?

---

First, as stated by @Kurt_Bremser we don't know your final goal here. That would be very helpful information.

There are only very rare cases where you need to put statistics like count and sum into a macro variable, and in fact there are better ways to determine these values than SQL. So I think the efficiency of what you are doing could improve, if only we knew what the final goal is.

[Tom]

If you want to put two values from the same observation into a single macro variable then use SAS code to concatenate the values first.

select catx(' -> ',count(x),sum(x)) into :a trimmed from one; 

It you want to select values from multiple observations into a single macro variable then use the SEPARATED BY clause.  If you want to control the order then include an ORDER BY clause.

  select value 
    into :b separated by ' -> '
    from (
     select 2 as order,sum(x) format=best32. as value from one
      union 
     select 1 as order,count(x) format=best32. as value from one 
      ) 
    order by order
  ;