topic Re: PROC SQL question in SAS Programming

PROC SQL question

Maisha_Huq — Fri, 17 Apr 2015 17:43:12 GMT

Hello:

I have some questions regarding proc SQL. My main question is: when I am expecting my output table to have three observations, why does it have 200+ observations?

As background:

The code:

proc sql;

create table estabgroup_table as

select mean_estabgroupamp.variable,

estabgroupamp,

estabgroupsst,

estabgroupnur,

estabgrouphih,

Prob,

Pooledmean

from mean_estabgroupamp,

mean_estabgroupsst,

mean_estabgroupnur,

mean_estabgrouphih,

anova_estabgroup1,

pooledmean1;

quit;

proc print data=estabgroup_table; run;

My input data sets into the SQL table are:

- five proc means output datasets

- proc anova data sets

Specifically:

1. Input data set for pooled means column

Input data set for other mean_estabgroup amp/sst/nur/hih columns are:

The desired output:

Obs	Variable	estabgroupamp	estabgroupsst	estabgroupnur	estabgrouphih	F	PROB	pooledmean
1	fns
2	parent_revenue
3	firm_employees_parent1

Sample actual output:

Ideas?

Many thanks for your time!

Re: PROC SQL question

Reeza — Fri, 17 Apr 2015 17:54:30 GMT

You don't post what you expect your output to look like but I expect you want a merge and you haven't specified any criteria on how to join the data so I'm not even sure how SQL would evaluate it. It looks like a cross join I guess, joining all tables to all records of each table. Specify a join condition

Re: PROC SQL question

Maisha_Huq — Fri, 17 Apr 2015 19:13:41 GMT

Thanks, Reeza!

Question, though: I tried correcting my code by specifying for SQL to join each table by the variable called "variable" as follows

proc sql;

create table estabgroup_table as

select mean_estabgroupamp.variable,

estabgroupamp,

estabgroupsst,

estabgroupnur,

estabgrouphih,

Prob,

Pooledmean

from mean_estabgroupamp as mea inner join

mean_estabgroupsst as mes inner join

mean_estabgroupnur as men inner join

mean_estabgrouphih as meh inner join

anova_estabgroup1 as an inner join

pooledmean1 as pm

on mea.variable eq mes.variable eq men.variable eq meh.variable eq a.variable eq pm.variable eq ;

quit;

proc print data=estabgroup_table; run;

but did I do this incorrectly because I get the following log result?:

2900 proc sql;

2901 create table estabgroup_table as

2902 select mean_estabgroupamp.variable,

2903 estabgroupamp,

2904 estabgroupsst,

2905 estabgroupnur,

2906 estabgrouphih,

2907 F,

2908 Prob,

2909 Pooledmean

2910 from mean_estabgroupamp as mea inner join

2911 mean_estabgroupsst as mes inner join

-----

2912 mean_estabgroupnur as men inner join

-----

2913 mean_estabgrouphih as meh inner join

-----

2914 anova_estabgroup1 as an inner join

-----

ERROR 73-322: Expecting an ON.

2915 pooledmean1 as pm

2916 on mea.variable eq mes.variable eq men.variable eq meh.variable eq an.variable eq pm.variable eq

2916! ;

2916 on mea.variable eq mes.variable eq men.variable eq meh.variable eq an.variable eq pm.variable eq

2916! ;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING,

USER.

2917 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Re: PROC SQL question

ballardw — Fri, 17 Apr 2015 19:21:45 GMT

Many of the joins want to know how to match up records, that is the ON requirement

proc sql;

create table new

as select a.*, b.onevariable

from thistable as a left joint thattable as b

on a.idvariable=b.idvariable;

quit;

Your join of multiple sets likely will need parentheses to get the order and each join, unless Cartesian, wants the ON criteria. Also with variables from separate table you may need to specify in the SELECT statement which table as in Meh.Variable to get the correct one.

Or possibly you want to do a datastep merge.

Re: PROC SQL question

Maisha_Huq — Fri, 17 Apr 2015 20:11:04 GMT

Thanks, ballardw! Cleaning through my code on how I did the joins and specifying which tables to get each selected variable from solved the problem.

I'm wondering: for the following table

Obs	Variable	estabgroupamp	estabgroupsst	estabgroupnur	estabgrouphih	F	PROB	pooledmean
1	fep mean(std)	144.1( 126.1)	167( 122.6)	207.5( 132.4)	132.8( 117.8)	210.59	1.7722E-134	147.4
2	fns mean(std)	40.9( 49.3)	82.8( 68.2)	82.8( 64.2)	44( 51.7)	2416.83	0	48.6
3	parent_revenue mean(std)	4.4( 9.1)	19.1( 177.1)	11.1( 75.4)	19.1( 648.7)	.	.	11.7

given columns 3-6 stand for establishment's (variable name: estabgroup) with valid values of either amp/sst/nur/or hih,

how could I add a row of the frequencies for each valid value of this estabgroup variable?

Thank you so much for your time and help!