SAS Error 180-322 when using tagsets.excelxp on mainframe

[rohitkrishna]

Hi all,

I'm facing some error in sas mainframe code could you help out for this problem below i mention the log results plz go through on it 

1 ODS TAGSETS.EXCELXP FILE= "C:\USERS\IBM_ADMIN\DESKTOP\ODSFILE.XML";
            _______________
            _______________
          180
         180
        180
      ERROR 180-322: Statement is not valid or it is used out of proper order.
     ERROR 180-322: Statement is not valid or it is used out of proper order.
      ERROR 180-322: Statement is not valid or it is used out of proper order.
2 PROC TABULATE DATA=SASHELP.CLASS;
3 CLASS SEX AGE;
4 VAR HEIGHT;
5 TABLE AGE,SEX*HEIGHT*MEAN;
6 RUN;

!
ODS TAGSETS.EXCELXP CLOSE;
_______________
_______________
_______________
180
180
180
180-322: Statement is not valid or it is used out of proper order.
180-322: Statement is not valid or it is used out of proper order.
180-322: Statement is not valid or it is used out of proper order.
The SAS session used 0.02 CPU seconds and 6799K.

plz go through on the code kindly give some solution for that 

Thanks & regards

rohitkrishna 

[Tom]

Mainframe?  If you are running on an IBM mainframe how are you going to write to file location like C:...?  That is something would work on a PC.

Macro?  I don't see any macro code in your log.  Not even any macro variable references.

The error message seems to be that TAGSETS.EXCELXP is not understood.

What version of SAS are running?

What is your ultimate goal with this program?

[rohitkrishna]

Hi Tom,
thanks for the quick replay
my goal is to export that date by using that method
and one I'm new to the mainframe environment so kindly suggest the right way to execute the date
Thanks & regards
rohitkrishna

[Kurt_Bremser]

SAS on a mainframe has only access to the mainframe's environment. This means you either have to use z/OS-style filenames (eg USER.SAS.FILE.OUT, see https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.3.0/com.ibm.zos.v2r3.idad400/name.htm), or you have a DSN defined in the encapsulating JCL script, see https://www.ibm.com/support/knowledgecenter/zosbasics/com.ibm.zos.zjcl/zjclc_jclDDstmt.htm.

After you have created a file on the mainframe, you can retrieve it with a file transfer utility, as mainframes today provide FTP and SSH access.

Note that tagsets.excelxp is rather new in SAS (Version 9.2, IIRC).

[Tom]

You need to talk to your shop about how to create output files on your system.

It might help to start by just creating simple output files first.  Say just a text file.

filename out 'my.out.txt' disp=new;
data _null_;
 set sashelp.class;
 file out dsd dlm=',';
 put (_all_) (+0);
run;

Then you can ask what tools your shop has for downloading those to your PC.

[Kurt_Bremser]

I corrected your misleading subject line.

And PLEASE use the {i} button for posting Logs! The main posting window messes with the horizontal positioning of ERROR messages, so it is often hard to detect what SAS is complaining about. The {i} feature has been set up explicitly for such things, so PLEASE use it!

[rohitkrishna]

Hi KurtBremser,
thanks for the replay
sorry about the non-proper code for the next post onwards I will work on it and kindly post the solution for the problem
Thanks & regards
rohitkrishna

[ghosh]

The error code is pointing to a missing semi-colon. please post your code

[rohitkrishna]

Hi Ghosh,
Thanks for the replay
please find below the mentioned code

{
000700 //SYSIN DD *
000800 ODS TAGSETS.EXCELXP FILE= "C:\USERS\IBM_ADMIN\DESKTOP\ODSFILE.XML";
000900 PROC TABULATE DATA=SASHELP.CLASS;
001000 CLASS SEX AGE;
001100 VAR HEIGHT;
001200 TABLE AGE,SEX*HEIGHT*MEAN;
001300 RUN;
001400 ODS TAGSETS.EXCELXP CLOSE;

}
thanks & regards
rohitkrishna

[Cynthia_sas]

Hi:

Changing only the location, your code works for me in SAS on Windows version 9.4 M5:

If you are running this course on the mainframe, writing to a C: drive location will not work. How you write to a location on the mainframe in a mainframe version of SAS depends on whether you are writing to a Unix/HFS file system attached to the mainframe or writing to an MVS mainframe cataloged dataset. For a mainframe dataset, it will make a difference whether you are writing to a sequential file or to a member of a partitioned dataset.

The code you sent works for me. I suspect the issue is that TAGSETS.EXCELXP might not be on your version of SAS on the mainframe or else there is some other problem elsewhere in your code.

You might want to open a track with Tech Support on this issue. How you are running the code and the version of SAS you're using might be impacting the job.

Cynthia

[rohitkrishna]

Hi all,
thanks for the quick replay
right now we are using 8.1 sas version in mainframe
so kindly give the solution for that
Thanks & regards
rohitkrishna

[Kurt_Bremser]

---

@rohitkrishna wrote:
Hi all,
thanks for the quick replay
right now we are using 8.1 sas version in mainframe
so kindly give the solution for that
Thanks & regards
rohitkrishna

---

8.1? Really? Why not use stone tablets with hammer & chisel?

(https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-so...)

TAGSETS.EXCELXP was added with SAS Version 9.1.3, as my log of your step tells me:

24         ODS TAGSETS.EXCELXP FILE= "$HOME/sascommunity/ODSFILE.XML";
NOTE: Writing TAGSETS.EXCELXP Body file: $HOME/sascommunity/ODSFILE.XML
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015). Add options(doc='help') to the ods 
statement for more information.
25         PROC TABULATE DATA=SASHELP.CLASS;
26         CLASS SEX AGE;
27         VAR HEIGHT;
28         TABLE AGE,SEX*HEIGHT*MEAN;
29         RUN;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The PROCEDURE TABULATE printed page 1.
NOTE: PROZEDUR TABULATE used (Total process time):
      real time           0.09 seconds
      cpu time            0.03 seconds
      

30         ODS TAGSETS.EXCELXP CLOSE;

I can only advise that you migrate to the current version of SAS ASAP. You are missing out on so many nice features of the language that it really costs you BIG MONEY at the moment in wasted time and missed opportunities.

[rohitkrishna]

HI KurtBremser,
thanks for the replay
so the main issue with the old sas version
what is exact code for the old version to get the output
please suggest some solution on the present error
Thnaks & regards
rohitkrishna

[Kurt_Bremser]

---

@rohitkrishna wrote:
HI KurtBremser,
thanks for the replay
so the main issue with the old sas version
what is exact code for the old version to get the output
please suggest some solution on the present error
Thnaks & regards
rohitkrishna

---

Simple version: you can't. Back the in the stone age, only Windows installations of SAS could produce Excel files (of the equally archaeologic .xls variety). TAGSETS.EXCELXP was a temporary remedy for that, until the platform-independent DBMS=XLSX came around.

There is one way that has worked back with SAS 6.X when I started working with SAS, still works today, and will work long after my bones lie bleaching in the sun: write a csv file with a data step, and read that into Excel. Mind that you can only transfer data; no fancy typesets, no coloring, just data as formatted by SAS in datasets. Maxim 27: Textuality Rules.