topic Using Prompt in file title while exporting to excel in SAS Programming

Using Prompt in file title while exporting to excel

JMart — Wed, 09 May 2018 15:30:04 GMT

I'm trying to export a SAS dataset to Excel 2013 file using a program within a process flow in SAS EG 7.1. I wish to use two prompt in the name of the file e.g : &Prompt_4_SSC_4147_EMPL_AND_POS_SDMB_FINAL_&Prompt_3.xlsx. Is it possible to do?

Re: Using Prompt in file title while exporting to excel

Kurt_Bremser — Wed, 09 May 2018 17:34:23 GMT

Values from prompts are handed to SAS code in the form of macro variables, and can be used as such.

Note that the names of macro variables used as parts of words need to be terminated with a dot:

&Prompt_4._SSC_4147_EMPL_AND_POS_SDMB_FINAL_&Prompt_3..xlsx

assuming that your prompts are named prompt_3 and prompt_4, respectively.

Re: Using Prompt in file title while exporting to excel

JMart — Wed, 09 May 2018 18:43:59 GMT

Hello,

Thank you for your solution. However I still get an error message. Here's my code:

PROC EXPORT DATA=WORK.FILTER_FOR_QUERY_FOR_SSC_EMPL_AN
FILE="filepath\&Tracker_4147._filename_&Date_Prod_Today."
DBMS=EXCEL REPLACE;

RUN;

I get the following error :
ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Do you know what cause this?

Note that SAS/Access to PC files is licensed and I use SAS EG 7.1

Re: Using Prompt in file title while exporting to excel

Kurt_Bremser — Wed, 09 May 2018 19:48:35 GMT

The ERROR happens in a libname statement, not in proc export code. Please post the log "as is" in a {i} window.

Re: Using Prompt in file title while exporting to excel

JMart — Fri, 11 May 2018 18:10:30 GMT

Here's the log that I get from the error:
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='R:\XXXX\REPORTS
5 ! COMPLETED\0000_ThursdayRecurring\ThursdayRecurring.egp';
6 %LET _CLIENTPROJECTPATHHOST='XXXXX';
7 %LET _CLIENTPROJECTNAME='ThursdayRecurring.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 PROC EXPORT DATA=WORK.FILTER_FOR_QUERY_FOR_SSC_EMPL_AN
27 FILE="R:\XXXX\REPORTS
27 ! COMPLETED\0000_ThursdayRecurring\Output Files\&Tracker_4147._SSC_4147_EMPL_AND_POS_SDMB_FINAL_&Date_Prod_Today."
WARNING: Apparent symbolic reference TRACKER_4147 not resolved.
WARNING: Apparent symbolic reference DATE_PROD_TODAY not resolved.
28 DBMS=EXCEL REPLACE;
ERROR: DBMS type EXCEL not valid for export.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

29 RUN;
30
31 GOPTIONS NOACCESSIBLE;
32 %LET _CLIENTTASKLABEL=;
33 %LET _CLIENTPROCESSFLOWNAME=;
34 %LET _CLIENTPROJECTPATH=;
35 %LET _CLIENTPROJECTPATHHOST=;
36 %LET _CLIENTPROJECTNAME=;
37 %LET _SASPROGRAMFILE=;
38 %LET _SASPROGRAMFILEHOST=;
39
40 ;*';*";*/;quit;run;
41 ODS _ALL_ CLOSE;
42
43
44 QUIT; RUN;

Re: Using Prompt in file title while exporting to excel

Kurt_Bremser — Fri, 11 May 2018 21:15:02 GMT

You have two issues here:

first, there is a problem with the resolution of macro variables. Either you omitted the necessary dot after the macro variable names, or necessary macro variables are not defined:

WARNING: Apparent symbolic reference TRACKER_4147 not resolved.
WARNING: Apparent symbolic reference DATE_PROD_TODAY not resolved.

second, SAS/ACCESS to PC Files is either not installed or not licensed:

ERROR: DBMS type EXCEL not valid for export.

Both of these issues need to be dealt with.

If you write the data from Excel to a csv file, no license is needed and a simple data step can read the data.