Dick_Shryock Tracker

I'm having trouble sending an attachment via email

Dick_Shryock — Mon, 26 Aug 2013 17:26:45 GMT

I have the attached code in a codenode and when I run it the code create an XLSX file that I can read directly using Excel2010. But when it attempts to attach the file to an email, the email is produced but the attachment give an error message

options symbolgen mlogic;
%let TitleDate = %sysfunc(intnx(Month,%sysfunc(date()),-1,B),yymmd7. );
%put &Titledate;
%let Filename = SeenByEMRes.xlsx;
%put &Filename;
%let FullFileName = &TitleDate. &FileName;
%PUT &FullFileName;
PROC SQL;
   CREATE TABLE WORK.SeenByEMResident AS
   SELECT t1.NAME,
          /* Visits */
            (count   (   t1.PAT_ENC_CSN_ID   )) LABEL="Visits" AS Visits
      FROM WORK.QUERY_FOR_PAT_ENC_HSP_0003 t1
      GROUP BY t1.NAME;
QUIT;

proc export data=SeenByEMResident
outfile="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&fullfilename."
dbms=xlsx replace ;
sheet="Residents";
run ;

%PUT &fullfilename;
ods pdf close;
filename mailit email to="mcmulj@mmc.org"
cc="shryor@mmc.org"
bc=""
from="mcmulj@mmc.org"
attach="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&FullFileName."
/* multiple attachments go between parentheses and are separated by spaces */

subject="Resident Volume &Titledate";

data _null_;
file mailit;
put 'THIS IS AN AUTO GENERATED E-MAIL' ;
put;
put 'Hello,';
put;
put;
put' Please find attached an excel file with your results for:';
put;
put "----> Residency Volume For &Titledate <---";
put;
put;
put 'Joel McMullin';
put 'Center for Performance Improvement';
put 'Maine Medical Center';
put '(207) 662-6735';
run;
filename mailit clear;

And this is the log that was produced:

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET SYSLAST=WORK.QUERY_FOR_PAT_ENC_HSP_0003;
4          %LET _CLIENTTASKLABEL='Program1';
5          %LET _CLIENTPROJECTPATH='R:\EM Residents\Seen_By_EMRes\MH_MMC_ASAP_Seen_By_EMRes.egp';
6          %LET _CLIENTPROJECTNAME='MH_MMC_ASAP_Seen_By_EMRes.egp';
7          %LET _SASPROGRAMFILE=;
8
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
13       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.
13       ! css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "D:\sastemp\_TD7712_SASMETA2_\Prc2/"
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
14
15         GOPTIONS ACCESSIBLE;
16         options symbolgen mlogic;
17         %let TitleDate = %sysfunc(intnx(Month,%sysfunc(date()),-1,B),yymmd7. );
18         %put &Titledate;
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
2013-07
19         %let Filename = SeenByEMRes.xlsx;
20         %put &Filename;
SYMBOLGEN: Macro variable FILENAME resolves to SeenByEMRes.xlsx
SeenByEMRes.xlsx
21         %let FullFileName = &TitleDate. &FileName;
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
SYMBOLGEN: Macro variable FILENAME resolves to SeenByEMRes.xlsx
22         %PUT &FullFileName;
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
2013-07 SeenByEMRes.xlsx
23         PROC SQL;
24            CREATE TABLE WORK.SeenByEMResident AS
25            SELECT t1.NAME,
26                   /* Visits */
27                     (count   (   t1.PAT_ENC_CSN_ID   )) LABEL="Visits" AS Visits
28               FROM WORK.QUERY_FOR_PAT_ENC_HSP_0003 t1
29               GROUP BY t1.NAME;
NOTE: Table WORK.SEENBYEMRESIDENT created, with 19 rows and 2 columns.

30         QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

31
32         proc export data=SeenByEMResident
33         outfile="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&fullfilename.
33       ! "
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
2                                           The SAS System               13:18 Monday, August 26, 2013

34         dbms=xlsx replace ;
35         sheet="Residents";
36         run ;

NOTE: The export data set has 19 observations and 2 variables.
NOTE: "\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\2013-07 SeenByEMRes.xlsx"
      file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds

37
38         %PUT &fullfilename;
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
2013-07 SeenByEMRes.xlsx
39         ods pdf close;
40         filename mailit email to="mcmulj@mmc.org"
41           cc="shryor@mmc.org"
42           bc=""
43           from="mcmulj@mmc.org"
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
44         attach="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&FullFileName."
45         /* multiple attachments go between parentheses and are separated by spaces */
46
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
47         subject="Resident Volume &Titledate";
48
49
50         data _null_;
51         file mailit;
52           put 'THIS IS AN AUTO GENERATED E-MAIL' ;
53           put;
54           put 'Hello,';
55           put;
56           put;
57           put' Please find attached an excel file with your results for:';
58           put;
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
59           put "----> Residency Volume For &Titledate <---";
60           put;
61           put;
62           put 'Joel McMullin';
63           put 'Center for Performance Improvement';
64           put 'Maine Medical Center';
65           put '(207) 662-6735';
66           run;

NOTE: The file MAILIT is:
E-Mail Access Device

Message sent
      To:          "mcmulj@mmc.org"
      Cc:          "shryor@mmc.org"
3                                           The SAS System               13:18 Monday, August 26, 2013

      Bcc:         ""
      Subject:     Resident Volume 2013-07
      Attachments: "\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\2013-07
SeenByEMRes.xlsx"
NOTE: 14 records were written to the file MAILIT.
      The minimum record length was 0.
      The maximum record length was 58.
NOTE: DATA statement used (Total process time):
      real time           2.88 seconds
      cpu time            0.00 seconds

67           filename mailit clear;
NOTE: Fileref MAILIT has been deassigned.
68
69
70
71
72
73         GOPTIONS NOACCESSIBLE;
74         %LET _CLIENTTASKLABEL=;
75         %LET _CLIENTPROJECTPATH=;
76         %LET _CLIENTPROJECTNAME=;
77         %LET _SASPROGRAMFILE=;
78
79         ;*';*";*/;quit;run;
80         ODS _ALL_ CLOSE;
81
82
83         QUIT; RUN;
84

I have also attached a screenshot of the error message that I get when I attempt to open the attached xlsx file

Anyone got any ideas what I'm doing wrong???

Thanks, Dick Shryock

Trying to calculate various statistic for columns in a table

Dick_Shryock — Wed, 10 Jul 2013 15:20:18 GMT

Trying to calculate various statistic for columns in a table

This question is Not Answered.

Dick.Shryock Jul 10, 2013 9:57 AM

<:section class="j-original-message">

Currently Being Moderated

I have a table with 11 columns and 31 rows the first column is a description and the rest of the columns represent various counts for each description.

What I want to do in SAS is create a table that provides the following for columns 2 - 11:

Sum

Median

Mean

Max

This is really easy to do in excel but I can't seem to figure how to do it in SAS [Version EG.5.1 (5.100.0.12019) (32-bit)]

See attached Dataset .

Thanks, Dick Shryock

Sample Output Table with Column Sum and Statistics

Date	Admit	Obs	AMA	D/C	LWBS	Tranx	LBEC	LD	Expired	Day_Total
01/01/2013	44	0	1	130	0	1	1	0	0	177
01/02/2013	44	0	0	124	0	3	0	0	0	171
01/03/2013	41	0	0	156	2	3	0	0	0	202
Total	129	0	1	410	2	7	1	0	0	550
Median	44.0	0.0	0.0	130.0	0.0	3.0	0.0	0.0	0.0	177
Mean	43.0	0.0	.03	136.7	0.7	2.3	0.3	0.0	0.0	183
Max	44	0	1	156	2	.3	1	0	0	202

Trying to calculate various statistic for columns in a table

Dick_Shryock — Wed, 10 Jul 2013 13:57:28 GMT

I have a table with 11 columns and 31 rows the first column is a description and the rest of the columns represent various counts for each description.

What I want to do in SAS is create a table that provides the following for columns 2 - 11:

Sum

Median

Mean

Max

This is really easy to do in excel but I can't seem to figure how to do it in SAS EG.

Thanks, Dick Shryock

Example Data:

Date	Admit	Obs	AMA	D/C	LWBS	Trans	LBEC	LD	Expired	DayTotal
01/01/2013	44	0	1	130	0	1	1	0	0	177
01/02/2013	44	0	0	124	0	3	0	0	0	171
01/03/2013	41	0	0	156	2	3	0	0	0	202
Total	129	0	1	410	2	7	1	0	0	550
Median	44.0	0.0	0.0	130.0	0.0	3.0	0.0	0.0	0.0	177.0
Mean	43.0	0.0	0.3	136.7	0.7	2.3	0.3	0.0	0.0	183.3
Max	44	0	1	156	2	3	1	0	0	202

Turn off Auto Arrange in Join Tables - EG 43

Dick_Shryock — Wed, 06 Feb 2013 14:21:23 GMT

Good Day,

I have Auto Arrange turned off in EG project flow so that I can arrange the program, query and result objects in a manner that I can see the flow better. When I go into the join tables panel I can move the tables the tables around to my heart's content putting them in a logical arrangement allows one to see the joins. It looks real pretty. Then I close the panel. When I open it again all of my arranging has gone by the wayside and everything is scrunched up.

My question is "how can I preserve the arrangement that I so painstakingly created and not revert to the EG way of scrunching the up. I have attached a document showing a minimalist example of my issue.

Thanks,

the

Re: How to Display Results in Project Flow

Dick_Shryock — Tue, 15 May 2012 12:47:55 GMT

Can I do this right here???

LOG Follows

The SAS System

05:45 Tuesday, May 15, 2012

1	;';";*/;quit;run;
2	OPTIONS PAGENO=MIN;
3	%_eg_hidenotesandsource;
16
17	/* -------------------------------------------------------------------
18	Code generated by SAS Task
19
20	Generated on: Tuesday, May 15, 2012 at 05:54:56
21	By task: List Data
22
23	Input Data: WORK.AGE_LE_18_REVISITS
24	Server: SASMain
25	------------------------------------------------------------------- */
26
27	%_eg_conditional_dropds(WORK.SORTTempTableSorted);
28	/* -------------------------------------------------------------------
29	Sort data set WORK.AGE_LE_18_REVISITS
30	------------------------------------------------------------------- */
31
32	PROC SQL;
33		CREATE VIEW WORK.SORTTempTableSorted AS
34			SELECT T.Encounter, T.MedRecNum, T.VisibleName, T.Age, T.Sex, T.ArrivalDT, T.Complaint, T.DispositionDT, T.Disposition,
34	! T.Destination, T.CountMedRecNum
35		FROM WORK.AGE_LE_18_REVISITS as T
36	;

NOTE: SQL view WORK.SORTTEMPTABLESORTED has been defined.

QUIT;

NOTE: PROCEDURE SQL used (Total process time):

	real time	0.00 seconds
	cpu time	0.00 seconds

38	TITLE;
39	TITLE1 "Emergency Services";
40	TITLE2 "Emergency Department";
41	TITLE3 "Report Listing";
42	FOOTNOTE;
43	FOOTNOTE1 "Created by Dick Shryock, Manager Emergency IT";
44	FOOTNOTE2 "Generated by the SAS on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
45
46	PROC PRINT DATA=WORK.SORTTempTableSorted
47		NOOBS
48		LABEL
49		;
50		VAR Encounter MedRecNum VisibleName Age Sex ArrivalDT Complaint DispositionDT Disposition Destination CountMedRecNum;
51	RUN;

WARNING: No output destinations active.

NOTE: PROCEDURE PRINT used (Total process time):

	real time	0.00 seconds
	cpu time	0.00 seconds

52	/* -------------------------------------------------------------------
53	End of task code.
54	------------------------------------------------------------------- */
55	RUN; QUIT;
56	%_eg_conditional_dropds(WORK.SORTTempTableSorted);
2	The SAS System	05:45 Tuesday, May 15, 2012

NOTE: View WORK.SORTTEMPTABLESORTED has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

	real time	0.00 seconds
	cpu time	0.00 seconds

57	TITLE; FOOTNOTE;
58
59
60	%_eg_hidenotesandsource;
71
72
73	QUIT; RUN;
74

Re: How to Display Results in Project Flow

Dick_Shryock — Tue, 15 May 2012 10:06:08 GMT

Thanks for the help, when I look at the log I see the following Warning:

WARNING: No output destinations active.

I don't believe that I explicitly turn off output destinations but the apparently are turned off none the less. How do I turn them on?

Can't seem to find an "OPTIONS" command that will do the trick.

Re: How to Display Results in Project Flow

Dick_Shryock — Wed, 25 Apr 2012 20:07:20 GMT

Alas that is checked also...

-d

Re: How to Display Results in Project Flow

Dick_Shryock — Wed, 25 Apr 2012 19:17:47 GMT

Yup HTML

-d

How to Display Results in Project Flow

Dick_Shryock — Wed, 25 Apr 2012 15:33:51 GMT

I have two different machines running EG 4.3. On one machine if I do a Tasks/Describe/List Data once the task runs the HTML Output shows up in my project flow. On the other machine it doesn't show the result. I have perused the Tools/Options settings but can't seem to find what I need to check to get the HTML output to show in the Project Flow on Both machines. Can anyone help me with this

question.

Thanks,

dick shryock

Can't seem to center my corporate Logo in HTML outout

Dick_Shryock — Wed, 14 Mar 2012 09:58:53 GMT

Thanks this does the trick

Can't seem to center my corporate Logo in HTML outout

Dick_Shryock — Tue, 13 Mar 2012 20:01:20 GMT

I have attached a document showing the output that I get as well as a code snippit. I can't seem to get the Logo to center in the report header. Seems like I should be using a Just=center pr something but can't seem to grok it.

Thanks for any help that you may be able to offer.

-d

How do I export and HTML File to a specific location in code

Dick_Shryock — Thu, 01 Mar 2012 01:12:16 GMT

Well that would be fine if there was a view last executed code so that I could add the code to my codenode. but alas it doesn;'y have that capability

How do I export and HTML File to a specific location in code

Dick_Shryock — Wed, 29 Feb 2012 19:12:35 GMT

I am trying to export an html file to s specific location and when I use ODS it put's it where I want but ignors my style:

ODS HTML BODY=\\sasmeta1\projects\mmc\warehouse\wdata\datasources\mailitattach\boarders.html

style=DicksStandard

Thanks, -d

Help Centering Corporate Logo using Proc Template

Dick_Shryock — Wed, 29 Feb 2012 14:24:00 GMT

Here is my Code:

Thanks in advance for any help given

dick shryock

Maine Medical Center

proc template ; 
     define style DicksStandard;
     parent=styles.EGdefault; 
     class body / preimage=\\sasmeta1\projects\mmc\warehouse\wdata\datasources\images\MMC_LOGOsidebyside_Small.GIF; 
     end; 
run;

DBMS =

Dick_Shryock — Wed, 21 Sep 2011 12:25:56 GMT

Art,

I'm trying to export to a file with a &variable imbedded in the file name, can I do that using the exel task or must I do it manually??

DBMS =

Dick_Shryock — Wed, 21 Sep 2011 10:49:52 GMT

You guys were right about the typo it is 9.2. I tried the EG Task however maybe I'm not using it right but I need to automate this project and name the file with a &variable imbeded in the name. I don't think that I can do that using the task. Hope that I'm wrong.

-d

DBMS =

Dick_Shryock — Tue, 20 Sep 2011 19:35:20 GMT

Hello All,

I am using Enterprise Guide 4.3 and SAS 6.2. I am trying to export a dataset to Excel and keep getting an error Message:

Here is my code:

/* Write data out to Excel file */

PROC EXPORT DATA = work.resident

OUTFILE = "\\sasmeta\projects\EG Projects\dick\A-4.3 Projects\Resident\SeenByResident&rptdate..xlsx"

DBMS = EXCEL2007 REPLACE;

RUN;

ERROR: DBMS type EXCEL2007 not valid for export.

Doesn't seem to matter what itteration of Excel I Use or whether I Name the file.xls or .xlsx.

Can someone help me with this,

Thanks,

Dick Shryock

My log produces this error message:

Having trouble creating a date variable to use in report headings

Dick_Shryock — Wed, 29 Jun 2011 10:27:46 GMT

Patrick, that was helpful but not exactly what I'm looking for (poorly worded question). I have a variable named RptDate that I want to use in the report heading. It could be any date or date range it is text.

I have tried "Emergency Department data for " &RptDate and "Emergency Department data for &RptDate" and both just produce the literal &RptDate.

I'm missing somthing very fundament here, but can't seem to put my finger on it.

Thanks,

-d

Having trouble creating a date variable to use in report headings

Dick_Shryock — Tue, 28 Jun 2011 21:09:45 GMT

Hi,

I would like to add a date to my reports both in the heading and the file name when I export them. Can someone help.

Thanks,

-d

Re: PROC EXPORT to Excel problem

Dick_Shryock — Thu, 16 Jun 2011 15:00:07 GMT

Thanks Art the Excelcs worked thanks for your help

=d