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

Hello,

 

I created a transport file using Proc Cport and the file is stored in mainframe. I then trasfer the file to my local machine using ftp. When I tried to read the fun using proc cimport in Mainframe SAS, I ran into the following errors. Please kindly help.

 

Thank you,

Vi

| 1    filename test 'C:\Users\NGUYEVE\Test.xpt';                              
| ERROR: Invalid file, C:\USERS\NGUYEVE\TEST.XPT.                              
| ERROR: Error in the FILENAME statement.                                      
| 2    libname dest 'C:\Users\NGUYEVE';                                        
| ERROR: Invalid physical name for library DEST.                               
| ERROR: Error in the LIBNAME statement.                                       
*------------------------------------------------------------------------------
                                                                             
 3    proc cimport infile=test library=dest;                                 
 4    run;                                                                   
                                                                             
 ERROR: Library DEST is not assigned.                                        
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

That's right -  external file instead is any file that is not a SAS dataset.
But to read external database you need license to SAS ACCESS to this database

and ther is a special syntax using SQL.

View solution in original post

15 REPLIES 15
Shmuel
Garnet | Level 18

You need transfer your cported file binary !

VNguyen
Calcite | Level 5

Hello,

 

I still received the same error. Below is the except from the job I run to create the cport file. The cported file is 'cagu.nguyeve.cport'. After that, I run the ftp on cmd.exe.

ftp> binary

ftp> get 'cagu.nguyeve.cport' C:\Test.xpt'

 //STEP00  EXEC PAPCRMS,TYPRUN='P'                               
 //DANFILE EXEC SAS                                              
 //WORK DD UNIT=(DISK,4),SPACE=(CYL,(2000,1000),RLSE)            
 //SASAUTOS DD DSN=UCLN23.FSDU.XTRACMAC,DISP=SHR                 
 //MACROS   DD DSN=SSTU.SAS.MACLIB,DISP=SHR                      
 //BFILE    DD DSN=BTB.P.A26,DISP=SHR                            
 //NONP1612 DD DSN=RIRU.SONGK3.TDFS.NONPRIME.LON.V1612,DISP=SHR  
 //DS1612   DD DSN=CAGU.NGUYEVE.CPORT,                           
 //         DISP=(,CATLG,DELETE),SPACE=(CYL,(2000,1500),RLSE),   
 //         UNIT=TSSU,DCB=(RECFM=FB,LRECL=80,BLKSIZE=8000)       
 //SYSIN DD *                                                    
 %MACRO NP (DSIN,DSOUT);                                         
 DATA NONPRIME;                                                  
 SET &DSIN..RBVFCL;                                              
 PROC CPORT DATA=NONPRIME FILE=&DSOUT MEMTYPE=DATA;              
 RUN;                                                            
Shmuel
Garnet | Level 18

Your post is not complete. Your last lines were:

//SYSIN DD *                                                    
 %MACRO NP (DSIN,DSOUT);                                         
 DATA NONPRIME;                                                  
 SET &DSIN..RBVFCL;                                              
 PROC CPORT DATA=NONPRIME FILE=&DSOUT MEMTYPE=DATA;              
 RUN;        

1) in your first post you note problem with proc CIMPORT while here you have proc CPORT.

2) You started with %MACRO NP ....  Where is the  rest of your macro ?

3) What arguments you supplied instead DSIN, DSOUT to execute the macro ?

4) The errors you posted are:

| 1    filename test 'C:\Users\NGUYEVE\Test.xpt';                              
| ERROR: Invalid file, C:\USERS\NGUYEVE\TEST.XPT.                              
| ERROR: Error in the FILENAME statement.                                      
| 2    libname dest 'C:\Users\NGUYEVE';                                        
| ERROR: Invalid physical name for library DEST.                               
| ERROR: Error in the LIBNAME statement.                       

  check again the path and filename. This has nothing with proc cport/cimport.

ballardw
Super User

Did you address this:

libname dest 'C:\Users\NGUYEVE';                                        
| ERROR: Invalid physical name for library DEST.                               
| ERROR: Error in the LIBNAME statement.   

You need a valid library before the CIMPORT will even run to look a the data to read. Make sure the path exists and that you have permissions to write there. Also if you are running as server version of SAS (is that what you mean by "mainframe') then the SERVER needs to see the folder and likely does not/cannot on your local harddrive. You may need to move the file to some where the server can see it.

 

VNguyen
Calcite | Level 5

Hello,

 

The library C:\Users\NGUYEVE is my own local folder. I am running SAS on TSO (I do not have BASE SAS if this info is somehow relevant). Do you think the server does not see my local folder then?

 

Thank you,

Vi

ballardw
Super User

I don't use TSO but from the ancient days i was working as systme that used JCL they did not ever see the hard drive of the local machine that was used to remote submit batch jobs.

 

And at this point I am a little confused. IS thif cport file created on the machine you are running the TSO job from? Then why??

 

VNguyen
Calcite | Level 5

Hi,

 

I need to extract data from mainframe and store them in Microsoft SQL Sever (the sever doesn't exist yet). However, the first thing I need to do is to see what appropriate data to extract. I thought that using SAS in TSO, I can first create a cport file (through running a batch job), transfer it to my local machine and convert it to some format that can be read in excel or something similar. Could you please let me know some better ways to do this?

 

Thank you,

Vi

ballardw
Super User

You should investigate SAS ACCESS to SQL Server. You might find that you can use that to make direct connection between your TSO SAS and the SQL Server. If that works you'll probably save your organization lots of headaches (though there may be new ones depending on what you do afterward) and time.

Peter_C
Rhodochrosite | Level 12
If you do not have a windows sas client the the cport file will not help.
You can create xml-style excel work books on the mainframe and download those
VNguyen
Calcite | Level 5

Hello,

 

Thank you all for your help. My supervisor decided to take another route so for now I would not need to tackle these issues. It may come up again in the future, which then i may need to ask for help with creating xml-style excel work books on the mainframe, as I don't have PC SAS (all the searches relating to xml-style excel work books  I found use PC SAS) and the mainframe datasets I tried to extract are huge (millions of records).

 

Regards,

Vi

Peter_C
Rhodochrosite | Level 12
Hi Vi



For files of that size on the mainframe we found tagsets.excelxp very
unsuitable. It works very well for volumes under a few thousand rows -
better if row count is in the hundreds.

My biggest challenge was getting the tagsets update code (downloaded from
the SAS website) to execute correctly - even though it is just base SAS
code, it is PROC TEMPLATE code involving perl regex and quite different
looking from data step and sql code - But, after making contacts at SAS
Global Forum (in 2007 I believe), the national language support features
were clarified and the client was greatly pleased with the quality of the
result delivered.

Now, it should all be much better as SAS now offer a better ODS destination
. However, I'm still not sure it would be so great for the filesize you
describe - nor sure how well excel would cope.





##- Please type your reply above this line. Simple formatting, no
attachments. -##
VNguyen
Calcite | Level 5

Hello,

 

Updated news is that I have PC SAS now. While trying to read the sasdataset that was created by PROC CIMPORT I received errors as you can see below (lines 130-135). However, when I used the path directly within the data step (lines 136-154), SAS is able to read the file. Could you please let me know why?

 

Thank you,

Vi

 

130  filename newfile 'C:\Users\NGUYEVE\nonprime.sas7bdat';
131
132
133  data newtest;
134  infile newfile;
135  run;

NOTE: The infile NEWFILE is:
      Filename=C:\Users\NGUYEVE\nonprime.sas7bdat,
      RECFM=V,LRECL=256,File Size (bytes)=609059840,
      Last Modified=21Feb2017:13:36:24,
      Create Time=21Feb2017:13:36:20

NOTE: 0 records were read from the infile NEWFILE.
NOTE: The data set WORK.NEWTEST has 1 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


136
137  proc print data=newtest (obs = 10);
138  run;

NOTE: No variables in data set WORK.NEWTEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


139
140
141  data newtest;
142  set 'C:\Users\NGUYEVE\nonprime.sas7bdat';
143  run;

NOTE: There were 557592 observations read from the data set C:\Users\NGUYEVE\nonprime.sas7bdat.
NOTE: The data set WORK.NEWTEST has 557592 observations and 100 variables.
NOTE: DATA statement used (Total process time):
      real time           1.68 seconds
      cpu time            1.66 seconds


144
145  proc print data=newtest (obs = 10);
146  run;

NOTE: There were 10 observations read from the data set WORK.NEWTEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.07 seconds
      cpu time            0.04 seconds


147
148
149  data newtest;
150  set 'C:\Users\NGUYEVE\nonprime.sas7bdat';
151  run;

NOTE: There were 557592 observations read from the data set C:\Users\NGUYEVE\nonprime.sas7bdat.
NOTE: The data set WORK.NEWTEST has 557592 observations and 100 variables.
NOTE: DATA statement used (Total process time):
      real time           1.66 seconds
      cpu time            1.63 seconds


152
153  proc print data=newtest (obs = 100);
154  run;

NOTE: There were 100 observations read from the data set WORK.NEWTEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.35 seconds
      cpu time            0.35 seconds

 

 

Shmuel
Garnet | Level 18

Pay attention:

 

INFILE is used to read external file, while SET is used to input sas dataset.

You file has the suffix sas7bdat which means it is a sas dataset.

This explains why INFILE failed while SET succeded.

VNguyen
Calcite | Level 5

Thank you. I had understood that an external file is a file that is stored outside a SAS folder (Work folder for example). Should I understand an external file instead to be any file that is not a SAS dataset?

 

Regards,

Vi

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 15 replies
  • 1253 views
  • 0 likes
  • 4 in conversation