topic ODS HTML FILE Query in SAS Programming

ODS HTML FILE Query

MeghnaRoy9 — Thu, 22 Aug 2019 15:52:51 GMT

Can anyone please tell me what's wrong with the following code which I'm using to call the Macro?
Log doesn't prompt any error but there isn't any external Excel file which is being created.

please help, I'm new!

ODS HTML FILE="/folders/myfolders/sasuser.v94/SP.XLS";
%FINE_CLASS(CSD,INS,11175,21089,AcctAge,10);
ODS HTML CLOSE;

Re: ODS HTML FILE Query

Reeza — Thu, 22 Aug 2019 16:00:54 GMT

Without the code in the middle it's anyone's guess.

There's either something in the log that shows an error,warning, or note.

I would first try your code as a non-macro version and see if that works.

Re: ODS HTML FILE Query

ballardw — Thu, 22 Aug 2019 16:15:06 GMT

It is getting to be a worse idea to lie to your computer such as naming a file with an XLS extension when the actual created file is HTML. Excel is getting pretty picky about opening such lies in the latest versions.

If you want spreadsheet output use ODS EXCEL.

Re: ODS HTML FILE Query

MeghnaRoy9 — Thu, 22 Aug 2019 16:17:00 GMT

I tried and this is what it prompted

%FINE_CLASS(CSD,INS,11175,21089,AcctAge,10);

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

What should I do now?

I have received a set of codes and I have to just input the value from the Excel file to generate a data. If required I can attach the files here.

Kindly let me know.

Re: ODS HTML FILE Query

MeghnaRoy9 — Thu, 22 Aug 2019 16:18:40 GMT

But I have used ODS HTML to open Excel files and it has run just fine. It's only in this case that I'm facing a problem so I'm assuming the problem Is with the format of the code.

Re: ODS HTML FILE Query

Reeza — Thu, 22 Aug 2019 16:51:53 GMT

Post your exact full log. That isn't enough information to solve your problem. Often the log has more details and where the errors occur can be indicative of the issues.

Especially if you didn't copy and paste the code correctly or if you had code before the lines shown - in fact I suspect the actual error is before the code shown.

ODS HTML QUERY

MeghnaRoy9 — Thu, 22 Aug 2019 16:51:57 GMT

Can anyone please tell me what's wrong with the following code which I'm using to call the Macro?
Log doesn't prompt any error but there isn't any external Excel file which is being created.

please help, I'm new!

ODS HTML FILE="/folders/myfolders/sasuser.v94/SP.XLS";
%FINE_CLASS(CSD,INS,11175,21089,AcctAge,10);
ODS HTML CLOSE;

Re: ODS HTML QUERY

Reeza — Thu, 22 Aug 2019 16:57:46 GMT

FYI - I merged your two threads because they are the same issue.

Please do not post the same question multiple times, especially after you start getting responses. Duplicate threads makes it harder to follow the threads and information will be in one post and not another leading to confusion.

Re: ODS HTML FILE Query

MeghnaRoy9 — Thu, 22 Aug 2019 16:57:49 GMT

The following are the codes I had pasted before finally calling the macro. Every code worked fine and the error popped up while calling the Macro. Hope this helps!

libname CSD "/folders/myshortcuts/SAS_Assignment";Run;

PROC IMPORT DATAFILE="/folders/myshortcuts/SAS_Assignment/Cross Sell_Data.csv"
OUT=CSD.cross_sell_data
DBMS=CSV
REPLACE;
RUN;

DATA CSD.DEV CSD.VAL;
SET CSD;
IF RANUNI (1000)<= 0.7 THEN OUTPUT CSD.DEV;
ELSE OUTPUT CSD.VAL;
RUN;

PROC SQL;CREATE TABLE L AS SELECT * FROM CSD;
SELECT COUNT (*) FROM L WHERE INS=1;QUIT;

PROC SQL;CREATE TABLE L AS SELECT * FROM CSD;
SELECT COUNT (*) FROM L WHERE INS=0;QUIT;

%MACRO FINE_CLASS (DSN,DEPVAR,TOT_RESP,TOT_NRESP,CLASS_VAR,GRPS);

PROC RANK DATA =&DSN. GROUPS=&GRPS. OUT=BIN_DSN;
VAR &CLASS_VAR.;
RANKS BIN;
RUN;

PROC SQL;
CREATE TABLE FINE_CLASSED AS SELECT
COUNT(*) AS NOBS,
MIN(&CLASS_VAR.) AS MIN_VAL,
MAX(&CLASS_VAR.) AS MAX_VAL,
SUM(CASE WHEN &DEPVAR. = 1 THEN 1 ELSE 0 END) AS RESP,
SUM(CASE WHEN &DEPVAR. = 0 THEN 1 ELSE 0 END) AS NRESP

FROM BIN_DSN

GROUP BY BIN;
QUIT;

DATA FINE_CLASSED1;
SET FINE_CLASSED;
TOT_RESP=&TOT_RESP.;
TOT_NRESP=&TOT_NRESP.;
WOE=LOG(((RESP/TOT_RESP)+0.000000001)/((NRESP/TOT_nRESP)+0.000000001));
IV=((RESP/TOT_RESP)-(NRESP/TOT_NRESP))*WOE*100;
RUN;

PROC PRINT DATA = FINE_CLASSED1;RUN;
/*PROC DELETE DATA = FINE_CLASSED1;*/
/*PROC DELETE DATA = FINE_CLASSED;*/
/*PROC DELETE DATA = BIN_DSN;RUN;*/

%MEND;

ODS HTML FILE="/folders/myfolders/sasuser.v94/SP.XLS";
%FINE_CLASS(CSD,INS,11175,21089,AcctAge,10);
ODS HTML CLOSE;

Re: ODS HTML FILE Query

Reeza — Thu, 22 Aug 2019 18:03:48 GMT

Add the following to the top of your code, run it and post the log please.

options mprint symbolgen;
<rest of your sas code>

Re: ODS HTML FILE Query

MeghnaRoy9 — Fri, 23 Aug 2019 09:18:26 GMT

Hey!

I tried running the code you gave but it didn't work. I had to make a few changes in the code and it's fine now.
Thank You for the help!

Re: ODS HTML FILE Query

Reeza — Fri, 23 Aug 2019 15:17:01 GMT

@MeghnaRoy9 wrote:

Hey!

I tried running the code you gave but it didn't work. I had to make a few changes in the code and it's fine now.
Thank You for the help!

It wouldn't have solved your problem, it would have put more information to the log to allow you to find the problem more easily.