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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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.

JMart
Fluorite | Level 6
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


JMart
Fluorite | Level 6
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;
Kurt_Bremser
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1674 views
  • 1 like
  • 2 in conversation