Hi All,
Very new to EMiner,I have a requirement where the output of scorecard node need to be produced in excel/CSV files. As of now i am using code node to produce the output, by defaultly it is producing in rtf or pdf files.
I understood that %EM_Register and %EM_Report macros are executing at backend to produce the desire output. in %EM_Register the default extension is .rtf i tried to change it to csv didnt suceeded.
Is there any way i can modify this macros to get output in excel/csv files are do i need to write build own code node?
below macros are from highlighted code node code.
/* Model Parameters */
%em_register(type=data, key=params);
%em_register(type=data, key=fitstat);
%em_register(type=data, key=corr);
%em_register(type=data, key=vif);
%em_register(type=data, key=sc_data);
%em_register(type=file, key=REPORT, extension=csv); /*
%ModelTable(twostg=N);
%sc_output(recalc=Y);
%sc_print;
%em_report(key=params, viewtype= DATA, autodisplay=Y,
block=%bquote(RMM Reports),
description=%bquote(Model Parameters), spk=&spk_keep );
%em_report(key=vif, viewtype=data, autodisplay=y,
block=%bquote(RMM Reports),
description=%bquote(Variance Inflation Factors table));
%em_report(key=vif, viewtype=bar, autodisplay=y,
block=%bquote(RMM Reports),
description=%bquote(Variance Inflation Factors plot),
x=variable, freq=vif);
%em_report(key=corr, viewtype=data, autodisplay=y,
block=%bquote(RMM Reports),
description=%bquote(Pearson Correlations table));
%em_report( key=REPORT, viewtype=fileviewer, autodisplay=Y,
block=%bquote(RMM Reports),
Your help is really appreciable
Hi Ray,
I got the desired utput thanks for your support.
%em_register(key=REPORT,type=FILE, extension=xls);
data x;
Var1='100';
Var2='200';
Var3='56045634';
run;
%em_report (KEY=REPORT, VIEWTYPE = FILEVIEWER, autodisplay=Y, block=%bquote(GRM Reports), DESCRIPTION=TESTREPORT);
ods html file="&em_user_REPORT";
proc print data=x;run;
ods html close;
Once you run the Scorecard node, all of its results are saved as datasets on the SAS server. So I think all you need to do is choose the ones you want and export them in csv format. It is pretty simple to do that in a code node, although there are other ways (opening the results and exporting them from Enterprise Guide, for example.)
You will need to know where to look on the SAS server and which results you want to export. Your EM flow diagram has an ID associated with it. If you look in properties you will see the ID, which will be emws1, emw2, etc. You can run Proc Contents to see the results files associated with that diagram.
All the results you are looking for will have the 'scorecard' prefix ('scorecard' is the Node ID for the Scorecard node). For example, the scorecard results are saved under 'scorecard_exportsscorecard' in the emw* folder. Fit statistics are saved as 'scorecard_emreportfit'.
So putting all of this together, you could run something like this in a Code node:
*run this to find the scorecard node results on the server.
proc contents data=emws1._all_;
run;
*do this for each piece of scorecard output you want to export as csv.;
proc export data=emws1.scorecard_exportscorecard
outfile='\\my_computer\scorecard.csv'
dbms=csv
replace;
run;
Here I'm assuming that the diagram ID is emws1. You may need to change that if your diagram has a different ID.
Hi Ray wright,
Thank your for your suggestion. Tried your suggestion its work. My client is looking something else below is requirement. When we execute code node with EM_Register and %EM_Report it produce reports. After right click on results it opens below window.
The default output of %EM_Report is in Rtf or PDF. when we click on view button(highlighted in red circle) it open in word document as the extension is in .rtf
Is there any way i can integrate the excel/csv/xml into view button.
All your inputs and suggestion is appreciated.
Well, you can do it. But it may not be a simple task. See, you would have to create the csv/xls/xml file via SAS code.
Here is some sample code where I create a simple CSV file. When I click on the View button, Excel launched.
We used this approach for a set of experimental nodes that would launch JMP from an EM results viewer and execute JMP Scripting Language code in JMP.
I don't know whether you'll want to use this approach, but it will give you an idea of what's involved.
Ray
%em_register(key=csvtest , type=FILE, extension=csv);
filename myfile "&em_user_csvtest";
data _null_;
file myfile;
put 'Year,Earnpct,Divpct,SP500,Earnings,Dividend';
put '1960,5.34,3.41,58.11,3.1,1.98';
put '1961,4.71,2.85,71.55,3.37,2.04';
run;
filename myfile;
%em_report (KEY=csvtest, VIEWTYPE = FILEVIEWER, autodisplay=Y,
DESCRIPTION=csv test);
Hi Ray,
Thanks for your suggestion. Added your code in report code editor of SAS code node. Unfortunately no CSV file was launched. but, a csv file was created in EMCode folder.
I tried your option earlier to create CSV output from SAS code,It execution fine but not no excel file is lanched. I want to integrate csv/excel output with view button. When we click on view button the output is opened in RTF file which i want to create in CSV/EXCEL output.
Request you to help to execute the code no matter how tricky/complex it is.
Hi Ray,
I got the desired utput thanks for your support.
%em_register(key=REPORT,type=FILE, extension=xls);
data x;
Var1='100';
Var2='200';
Var3='56045634';
run;
%em_report (KEY=REPORT, VIEWTYPE = FILEVIEWER, autodisplay=Y, block=%bquote(GRM Reports), DESCRIPTION=TESTREPORT);
ods html file="&em_user_REPORT";
proc print data=x;run;
ods html close;
Hi.
I'm a little surprised that it worked with extension=xls. On my end, I had to use extension=html, in which case the report opens in a browser.
But if it worked for you, great!
Ray
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.