a question about proc sql

[maomaochong96888_hotmail_com]

hi! Tom

below is the log... no error at all... and I copied your code and run them in my home's computer and it is OK... my company's sas is 9.1 on xp...

and this is a old problem for me and i cannot solve it. so every time i just avoid to use proc sql although i love it...

thanks again

106  data t;

107

108  input a $ x @@;

109

110  cards;

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

NOTE: The data set WORK.T has 5 observations and 2 variables.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

114  run;

115

116  proc sql ;

117

118    create table nocount as

119

120    select "no count bigger than 5" as comment from t where x > 5

121

122    ;

NOTE: Table WORK.NOCOUNT created, with 0 rows and 1 columns.

123

124    create table count as

125

126    select  count(*) as count

127

128         ,  "count bigger than 5" as comment from t where x > 5;

NOTE: Table WORK.COUNT created, with 1 rows and 2 columns.

129

130    create table wrongcount as

131

132    select  count('1','2') as count

133

134         ,  "count bigger than 5" as comment from t where x > 5

135

136    ;

NOTE: Table WORK.WRONGCOUNT created, with 0 rows and 2 columns.

137

138  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[Tom]

So that log is one that worked as expected, but it does not show the values that it created for the COUNT data set that has one observation, so we cannot tell if it is blank or has the expected value.

Run this program and if it still shows that COMMENT variable is missing then contact SAS Support.

873  proc sql ;

874    create table count as

875      select count(distinct name) as count,'comment' as comment

876      from sashelp.class where age > 100

877    ;

NOTE: Table WORK.COUNT created, with 1 rows and 2 columns.

878  quit;

NOTE: PROCEDURE SQL used (Total process time):

879  data _null_;

880    set count;

881    put (_all_) (=);

882  run;

count=0 comment=comment

NOTE: There were 1 observations read from the data set WORK.COUNT.

[anushakalyani]

Sure output returns with 0 because there are no records in x greater then 5.

[maomaochong96888_hotmail_com]

thanks anushakalyani

Yes, i see why there is 0 because no records in x > 5.  but i don't know why the variable comment is missing if the count =0. i think it should be "count bigger than 5"

but is count not missing, comment is always not missing...

and my point is not why count = 0. my point is why comment is missing if count =0. but if count>0 comment is always no missing. Maybe my poor english doesn't make me clear...

[Doc_Duke]

Your English is fine.  You may need to do as Tom said and provide your log, SAS version, and OS as well as your output.  Cut-and-paste will sometimes reveals things that our eyes miss in re-typing.

I ran your sample code with 32 bit SAS 9.3 TS1M2 on a Win 7 Pro 64 bit OS, and the sample reported 0 as well as the comment.

Doc Muhlbaier

Duke

[maomaochong96888_hotmail_com]

thanks for all of your help!!  this is what i got...  my sas is 9.1.3 on xp...

[maomaochong96888_hotmail_com]

sorry the picture is not clear...(don't know how to make it clear)

it(count dataset) looks like this:

count             comment

0                         

[Tom]

The text copied from LOG is of more value than a picture of output window.

That could have been the print from a previous step.  Or the SQL step could have failed to overwrite the dataset because it was open or some other issue.

[shmilycn]

Hello Tom, i can not get the right result with your codes. I have SAS 9.1 TS1M3 on Win 7 Pro 64bit.

387  proc sql ;

388   create table count as

389  select count(distinct name) as count,'comment' as comment

390  from sashelp.class where age > 100

391  ;

NOTE: Table WORK.COUNT created, with 1 rows and 2 columns.

392  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

393  data _null_;

394  set count;

395  put (_all_) (=);

396  run;

count=0 comment=

NOTE: There were 1 observations read from the data set WORK.COUNT.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

It seems that the results really depend on how SAS works.

I think that the functions in Proc SQL have an initial value, such as the function count has "0" and sum has ".". the columns like comment with no function maybe has a initial value "blank". so at the beginning when SAS initial the table T1, there is already one observation and the column count has a value 0  and the column comment has nothing. then the real value will be generated by the codes. under the condition "x > 5" returns no observation for caculation and the initial value will no changed. In the normal data and proc step SAS use an other Method for caculation. for example, use proc summsary with where condition to caculate the table T. if there is no observation, there will be also no observation in the results.

[Tom]

I get the same results as you using SAS 9.1.3  (TS1M3) and SAS 8.2 on Unix.

But it does populate the COMMENT variable on SAS 9.2 and SAS 9.3.  Looks like they changed the behavior starting with 9.2.

Perhaps you should report it to SAS support as a bug?

[maomaochong96888_hotmail_com]

Thanks TOM

I run your code in my computer and the comment is missing...

jennifer

[maomaochong96888_hotmail_com]

And Thanks PG and everyone who helps me!!!

Now I realize it is not my problem but sas software.... so far I can use PG's code to solve this problem...

have a nice day!

[loredana_cornea]

What if you were to create the comment variable as a macro variable in proc sql ???