Help using Base SAS procedures

PROC SQL NOWARN?

Reply
Regular Contributor
Regular Contributor
Posts: 170

PROC SQL NOWARN?

Hello all,

Is there is a certain version that PROC SQL Nowarn is supported?

I attempt to insert it as:

PROC SQL NOPRINT (FOR SOMETHING ELSE)  NOWARN;

...

QUIT;

THE NOPRINT works but not the other.

Thanks.

Lawrence

PROC Star
Posts: 7,492

PROC SQL NOWARN?

Proc SQL does have an undocumented nowarn option.  I'm not sure when it was introduced and, since it isn't documented, it may have been pulled.

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL NOWARN?

Quite possible..Thanks.

If so, I can always re-route the log file for a bit..

Respected Advisor
Posts: 3,799

Re: PROC SQL NOWARN?

I'm not sure which warning(s) you are trying to suppress.  NOWARN does suppress the WARNING associated with INOBS which may be the only one it affects.

611  options fullstimer=0;

612  proc sql noprint;

613     reset inobs=3;

614     create table class1 as select * from sashelp.class;

WARNING: Only 3 records were read from SASHELP.CLASS due to INOBS= option.

NOTE: Table WORK.CLASS1 created, with 3 rows and 5 columns.

615     %put _global_;

GLOBAL SQLOBS 3

GLOBAL SQLOOPS 13

GLOBAL SYS_SQL_IP_ALL -1

GLOBAL SYS_SQL_IP_STMT

GLOBAL SQLXOBS 0

GLOBAL SQLRC 4

GLOBAL SQLEXITCODE 0

616     reset inobs=4 nowarn;

617     create table class2 as select * from sashelp.class;

NOTE: Table WORK.CLASS2 created, with 4 rows and 5 columns.

618     %put _global_;

GLOBAL SQLOBS 4

GLOBAL SQLOOPS 14

GLOBAL SYS_SQL_IP_ALL -1

GLOBAL SYS_SQL_IP_STMT

GLOBAL SQLXOBS 0

GLOBAL SQLRC 4

GLOBAL SQLEXITCODE 0

619     quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.03 seconds

      cpu time            0.03 seconds

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL NOWARN?

Posted in reply to data_null__

DATA_NULL_;

The warning I get is:

WARNING: INPUT function reported 'WARNING: Illegal first argument to function' while processing WHERE

         clause.

This is a byproduct of where on occasion there is missing data in the dataset itself.

I have gotten around this issue by re-directing the log file-Amazing that even at 1/3 of the dataset, it generates a whopping 69 MB log file.

Lawrence

PROC Star
Posts: 7,492

PROC SQL NOWARN?

It would help if you post your entire proc sql code.  If it is simply the warning from an input function that you want to avoid you may be able to accomplish that by including ?? before the specified informat.

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL NOWARN?

Art,

The code is as follows for the PROC SQL part-Please note that is a larger part of a macro-

Thanks for looking at this-

Lawrence

%macro groupsxz (data=, var=,var1=,var2=,var3=);

   proc sort data=&data(keep=&var &var1 &var2 &var3) out=values nodupkey ;

    by &var3;

   run;

   data _null_;

      set values end=last;

      call symputx('sitea'||left(_n_),&var);

      call symputx('siteb'||left(_n_),&var1);

      call symputx('sitec'||left(_n_),&var2);

      if last then call symputx('count',_n_,'g');

   run;

%put _local_;

PROC PRINTTO LOG='p:\LOG.txt';

DATA _NULL_    ;

select(&var);

%do i=1 %to &count;

proc sql noprint nowarn;

create table RN as select distinct fixdate,

SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as RN format percent10.,

trim(left(compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')')))

as RNT

from SHAREPOINT as A

where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and

INPUT(SUBSTR(A.Observed_Role_,1,2),8.) = 1 and

UNIT in ("&&sitea&i")

GROUP BY FIXDATE, UNIT;

create table MD as select distinct fixdate,

SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as MD format percent10.,

trim(left(compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')')))

as MDT

from SHAREPOINT as A

where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and

INPUT(SUBSTR(A.Observed_Role_,1,2),8.) = 2 and

UNIT in ("&&sitea&i")

GROUP BY FIXDATE, UNIT;

create table RT as select distinct fixdate,

SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as RT format percent10.,

compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')')

as RTT

from SHAREPOINT as A

where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and

INPUT(SUBSTR(A.Observed_Role_,1,2),8.) = 3 and

UNIT in ("&&sitea&i")

GROUP BY FIXDATE, UNIT;

create table OTHER as select distinct  fixdate,

SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*) as OTHER format percent10.,

compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')')

as OTHERT

from SHAREPOINT as A

where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and

INPUT(SUBSTR(A.Observed_Role_,1,2),8.) NOT IN (1:3) and

UNIT in ("&&sitea&i")

GROUP BY FIXDATE, UNIT;

create table totals as select distinct fixdate,

SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*)

as P format percent10.,

compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')')

as PT

from SHAREPOINT as A

where intnx('month',today(),-12)<=fixdate<intnx('month',today(),0) and UNIT in ("&&sitea&i")

GROUP BY fixdate, UNIT;

create  table totalx as select distinct

A.*,A.fixdate as DATE, B.*,C.*,D.*,E.*

FROM RN AS A LEFT JOIN MD AS B ON A.FIXDATE=B.FIXDATE

             LEFT JOIN RT AS C ON A.FIXDATE=c.FIXDATE

             LEFT JOIN OTHER AS D ON A.FIXDATE=D.FIXDATE

             LEFT JOIN TOTALS AS E ON A.FIXDATE=E.FIXDATE;

select put(fixdate,monyy5.) as MONTH length=5,

CASE WHEN count(RNT)>0 THEN RNT ELSE "NO OBS" END AS RNT LENGTH=12,

CASE WHEN count(MDT)>0 THEN MDT ELSE "NO OBS" END AS MDG LENGTH=12,

CASE WHEN count(RTT)>0 THEN RTT ELSE "NO OBS" END AS RTT LENGTH=12,

CASE WHEN count(OTHERT)>0 THEN OTHERT ELSE "NO OBS" END AS OTHERT LENGTH=12,

PT length=12

INTO :MONTHG, :RNG, :MDG, :RTG, Smiley SurprisedTH, :TOTG

FROM totalx

WHERE fixdate=intnx('month',today(),-1);

QUIT;

PROC Star
Posts: 7,492

PROC SQL NOWARN?

Would code like the following correct the problem you mentioned?:

*create some test data;

data sharepoint;

  length name $10;

  set sashelp.class;

  if _n_ in (3,4,5) then name=

   cat('1'||' '||strip(name));

run;

proc sql noprint;

  create table RN as

    select *

      from SHAREPOINT as A

        where INPUT(SUBSTR(A.Name,1,2), ? 8.) = 1

  ;

quit;

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL NOWARN?

Art,

Sorry that does not resolve the issue-

No worries-I temporarily dump the information to a log file during the macro.

I was just seeing if there was other options.

Thanks for your help on this.

Lawrence

N/A
Posts: 1

Re: PROC SQL NOWARN?

Looks like your getting a warning on the INPUT function (when converting a datatype?).  You can suppress this error/warning message to the log by using the '?' modifier.

See http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000180357.htm

Super User
Posts: 10,048

Re: PROC SQL NOWARN?

Should be '??' modifier.

Super User
Posts: 5,441

Re: PROC SQL NOWARN?

?? is not supported in peoc SQL... :-(


Data never sleeps
Super User
Posts: 10,048

Re: PROC SQL NOWARN?

Oh? Thank you.

I don't even realize it .

Ask a Question
Discussion stats
  • 12 replies
  • 3870 views
  • 0 likes
  • 6 in conversation