BookmarkSubscribeRSS Feed
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

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

12 REPLIES 12
art297
Opal | Level 21

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.

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

Quite possible..Thanks.

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

data_null__
Jade | Level 19

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

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

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

art297
Opal | Level 21

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.

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

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, :OTH, :TOTG

FROM totalx

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

QUIT;

art297
Opal | Level 21

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;

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

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

DocGibby
Calcite | Level 5

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

Ksharp
Super User

Should be '??' modifier.

LinusH
Tourmaline | Level 20

?? is not supported in peoc SQL... 😞


Data never sleeps
Ksharp
Super User

Oh? Thank you.

I don't even realize it .

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