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
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.
Sure output returns with 0 because there are no records in x greater then 5.
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...
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
thanks for all of your help!! this is what i got... my sas is 9.1.3 on xp...
sorry the picture is not clear...(don't know how to make it clear)
it(count dataset) looks like this:
count comment
0
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.
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.
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?
Thanks TOM
I run your code in my computer and the comment is missing...
jennifer
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!
What if you were to create the comment variable as a macro variable in proc sql ???
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.