Pivot table generation using SAS EG UNIX remote server

Reply
Contributor
Posts: 25

Pivot table generation using SAS EG UNIX remote server

Hi,

Could anyone help me out in running VB sript to generate excel pivots in SAS EG remote server UNIX. code generated txt data file and VBS file but it didnt generate excel pivot. Please give me suggestions to run the code.

   %macro pivot(type, data=, var=, name=, orientation=, stat=, format=);
%if &type=workbook %then %do;
%global gblworkbook;
%let gblworkbook=&name;
data _null_;
set sashelp.class;
file "/prod/user1/XXXX/RawData.txt";
if _n_=1 then put "%sysfunc(translate(&var,'|',' '))";;
put %scan(&var,1) (%substr(&var,%index(&var,%str( )))) (+(-1) '|');
data _null_;
file "/prod/user1/XXXX/class.vbs";
put 'Set XL = CreateObject("Excel.Application")' / 'XL.Visible=True' /
'XL.Workbooks.OpenText "/prod/user1/xxxx/RawData.txt", 437, 1, 1, -4142, False, False, False, False, False, True, "|"';
%end;
%if &type=worksheet %then %do;
put "XL.Sheets.Add.name = ""&name""" /
"XL.ActiveSheet.PivotTableWizard SourceType=xlbase, xl.sheets(""RawData"").UsedRange, ""&name!R1C1"", ""pvttbl""";
%end;
%if &type=field %then %do;
%if &orientation=Data %then %do;
put "XL.ActiveSheet.PivotTables(""pvttbl"").AddDataField XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&name""), ""&Stat of &name"",
"
%if &stat=Sum %then "-4157";
%if &stat=Count %then "-4112";
%if &stat=Average %then "-4106";;
%end;
%else %do;
put "XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&name"").Orientation = "
%if &orientation=Page %then "3";
%if &orientation=Row %then "1";
%if &orientation=Column %then "2";
%end;;
%if &format^= %then
%if &stat^= %then
put "XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&stat of &name"").numberformat = " ""&format"";
%else
put "XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&name"").numberformat = " ""&format"";;
%end;
%if &type=resize %then
put "XL.ActiveSheet.Columns.AutoFit";;
%if &type=create %then %do;
put "XL.ActiveWorkbook.SaveAs ""&gblworkbook"", -4143";;
run;
  x '/prod/user1/XXXX/class.vbs' ;
%end;
%mend;

%pivot(workbook, name=/prod/user1/XXXX/class.xls, data=sashelp.class, var=Name Age Sex Height);

%pivot(worksheet, name=TestPivotTable1);
%pivot(field, name=Sex, Orientation=Page);
%pivot(field, name=Name, Orientation=Row);
%pivot(field, name=Height, Orientation=Data, Stat=Sum, Format="#,##0.00");
%pivot(resize);
%pivot(create);


PROC Star
Posts: 1,167

Re: Pivot table generation using SAS EG UNIX remote server

One option is that you could calculate the NWAY of your pivot using PROC MEANS or SUMMARY; this would then pretty much be the source for a pivot table, your users could simply create a pivot on the results and then drag variables.

Tom

Contributor
Posts: 25

Re: Pivot table generation using SAS EG UNIX remote server

Hi, Thanks for your response. I have around 200 files to create using this logic. Do you know UNIX code to execute X statements (to call vbScript) which can be written as X 'path' in windows SAS?

Thanks,

Raj

PROC Star
Posts: 1,167

Re: Pivot table generation using SAS EG UNIX remote server

The problem is that if your SAS server is Unix, there's no way to execute your VBScript program on it. Some options that I can think of:

1. Write your VBScript file to a mounted disk that is actually on a Windows machine, and then run the program on that machine;

2. Instead of creating your pivot table using VBScript, use one of the SAS Excel ODS destinations to create the actual spreadsheet, which your users can then open;

3. What I was suggesting in the earlier comment, just write the summarized data to a spreadsheet, and have your users create the pivots themselves.

Tom

Ask a Question
Discussion stats
  • 3 replies
  • 703 views
  • 0 likes
  • 2 in conversation