BookmarkSubscribeRSS Feed
SASs33ker
Calcite | Level 5

Hi there,

I would like to know if it is possible to write two proc SQL code blocks and store the two results into two different variables.Then create a third variable to store the division of the previous two variables.

Basically, I would like to count in column "status" all the "Yes" and store in variabable "a" and do another count in column "statu" but this time count all records and store the result in variable in "b".

Create a third variable  "c" to store the  a/b result.

Can this be done in one proc sql code block?

Any help or guidance is much appreciated.

Thank you

6 REPLIES 6
art297
Opal | Level 21

Is the following what you are trying to do?

proc sql;

  create table want as

    select sum(sex='M') as a,

           count(*) as b,

           calculated a/calculated b as c

      from sashelp.class

  ;

quit;

SASs33ker
Calcite | Level 5

Yes, except I would also like to use a group by clause somewhere in there. (sorry if I didn't mention it initially)

So, on the actual output table or view, I would like to group the a/b based on each program area, and there are about 20 program areas and I don't want to do this 20 times.

Will it be....

proc sql;

create table as resultSet as

select ProgramAreas, sum(sex='M') as a,

count(*) as b,

calculated a/calculated b as c

from table

group by Program Areas;

run;

Will this run without errors?

art297
Opal | Level 21

As long as you spell ProgramAreas consistently (you didn't).  The following worked for sashelp.class:

proc sql;

  create table want as

    select age,

           sum(sex='M') as a,

           count(*) as b,

           calculated a/calculated b as c

      from sashelp.class

        group by age

  ;

quit;

p.s.  end your proc sql calls with quit;  not run;

SASs33ker
Calcite | Level 5

Great, thank you. Will try it out tomorrow morning.

SASs33ker
Calcite | Level 5

Hi there,

can you explain what this line of code mean?

calculated a/calculated b as c

I"m having a hard time following it

art297
Opal | Level 21

It simply represents a/b but, since a and b are defined in the previous line they won't be accepted unless they are preceded by the string 'calculated'.

Thus 'calculated a' represents 'a that was calculated earlier'.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 21592 views
  • 0 likes
  • 2 in conversation