BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sss
Fluorite | Level 6 sss
Fluorite | Level 6

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?

Eminer_Nodes.PNG

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 Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
sss
Fluorite | Level 6 sss
Fluorite | Level 6

Hi Ray,

I got the desired utput Smiley Happy 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;

View solution in original post

6 REPLIES 6
rayIII
SAS Employee

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.



sss
Fluorite | Level 6 sss
Fluorite | Level 6

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.

eminer.png

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.

rayIII
SAS Employee

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);    

sss
Fluorite | Level 6 sss
Fluorite | Level 6

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.

Eminer_ouput.png

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.

Eminer_ouput2.png

sss
Fluorite | Level 6 sss
Fluorite | Level 6

Hi Ray,

I got the desired utput Smiley Happy 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;

rayIII
SAS Employee

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

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!

How to choose a machine learning algorithm

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.

Discussion stats
  • 6 replies
  • 2475 views
  • 5 likes
  • 2 in conversation