topic Re: Proc Tabulate Help in SAS Programming

Proc Tabulate Help

sasuser_sk — Tue, 06 Jul 2021 18:32:57 GMT

I have below code to run proc export that successfully sends my workbook to server and from server to local drive by Copy file. Then I have proc tabulate and I want to add the results from proc tabulate to the same workbook. Can anyone please make suggestions on how I can get the desired result. For proc tabulate sheet I want to name it as " Act Summary".

%let F_Name = Business Report;

%let folderloc = /sasdata/exports/;

%let Todays_Date = %SYSFUNC(DATE(), date9.);

%let Full_F_Name = &folderloc.&F_Name._&Todays_Date..xlsx;

%let backup = "&Full_F_Name..bak";

proc export data=WORK.Acts

outfile= "&Full_F_Name."

dbms=xlsx

replace;

Sheet= "ActDetails";run;

proc export data=WORK.Gross

outfile= "&Full_F_Name."

dbms=xlsx

replace;

Sheet= "GrossDetails";run;

PROC TABULATE

DATA=WORK.Acts;

VAR SRVC_ID;

CLASS "Highest"n / ORDER=UNFORMATTED MISSING;

CLASS "Act Month"n / ORDER=UNFORMATTED MISSING;

TABLE /* Row Dimension */

'Highest'n

ALL={LABEL="Total (ALL)"},

/* Column Dimension */

'Act Month'n* N* SRVC_ID

ALL={LABEL="Total (ALL)"}* N;;

RUN;

Re: Proc Tabulate Help

Reeza — Tue, 06 Jul 2021 19:54:02 GMT

If you change your two proc exports to a proc print instead you can use ODS EXCEL.

ods excel file='/home/fkhurshed/demo/&full_f_name..xlsx" options(sheet_interval='proc');
proc print data=acts;
run;

proc print data=gross;
run;

proc tabulate .....

....run;

ods excel close;

Re: Proc Tabulate Help

sasuser_sk — Tue, 06 Jul 2021 21:10:31 GMT

I tried your suggestion and it gave me insufficient memory issue. I tried using proc options option=memsize; run; quit; but that didn't help either.

Re: Proc Tabulate Help

Reeza — Tue, 06 Jul 2021 21:25:51 GMT

Does your proc tabulate run? If not, that's an entirely different issue.

Show your full code and log please.

Re: Proc Tabulate Help

ballardw — Wed, 07 Jul 2021 14:03:57 GMT

@sasuser_sk wrote:

I have below code to run proc export that successfully sends my workbook to server and from server to local drive by Copy file. Then I have proc tabulate and I want to add the results from proc tabulate to the same workbook. Can anyone please make suggestions on how I can get the desired result. For proc tabulate sheet I want to name it as " Act Summary".

%let F_Name = Business Report;

%let folderloc = /sasdata/exports/;

%let Todays_Date = %SYSFUNC(DATE(), date9.);

%let Full_F_Name = &folderloc.&F_Name._&Todays_Date..xlsx;

%let backup = "&Full_F_Name..bak";

proc export data=WORK.Acts

outfile= "&Full_F_Name."

dbms=xlsx

replace;

Sheet= "ActDetails";run;

proc export data=WORK.Gross

outfile= "&Full_F_Name."

dbms=xlsx

replace;

Sheet= "GrossDetails";run;

PROC TABULATE

DATA=WORK.Acts;

     VAR SRVC_ID;

     CLASS "Highest"n /     ORDER=UNFORMATTED MISSING;

     CLASS "Act Month"n / ORDER=UNFORMATTED MISSING;

     TABLE /* Row Dimension */

'Highest'n

ALL={LABEL="Total (ALL)"},

/* Column Dimension */

'Act Month'n* N* SRVC_ID

ALL={LABEL="Total (ALL)"}* N;;

RUN;

When you use code like this the second output overwrites the first

proc export data=WORK.Acts                                                                            
outfile= "&Full_F_Name."
  dbms=xlsx
  replace;
  Sheet= "ActDetails";run;                                                                                                                                   

proc export data=WORK.Gross                                                                            
outfile= "&Full_F_Name."

You used the same name for both outputs.

If you have both the ODS Excel and ODS Html destinations open at the same time then you are duplicating the output and large report tables may use up all the memory. You may need to have ODS HTML close; before generating the output.

If you still have issues with memory you may have to redesign the table to fit available memory.