topic Re: PROC SQL: Creating a table from a flat file (text file). in SAS Procedures

PROC SQL: Creating a table from a flat file (text file).

chandler — Tue, 09 Mar 2010 17:24:55 GMT

I'm using SAS 9.1.3 with Service Pack 4, on a Windows XP Professional platform. I've been reading data from a flat (text) file out on our mainframe via FTP to transfer the data and create a table/dataset on our Windows server, the traditional SAS way, in a Data Step with infile statements, input statements and column input. Can you do this in a Create Table statement in PROC SQL as well? I don't see it in the SAS SQL Procedure documentation. I've taken a course on SAS SQL 1: Essentials, but it didn't address the flat (text) file import question. Is it even efficient to try this in PROC SQL?

Re: PROC SQL: Creating a table from a flat file (text file).

sbb — Tue, 09 Mar 2010 18:18:03 GMT

You will want to share whatever SAS code you are using today for an accurate reply/feedback. Suggest includng a small data-sample from your mainframe file and a short PROC PRINT or PUTLOG _ALL_; SAS ouput data sample as well.

Scott Barry
SBBWorks, Inc.

Re: PROC SQL: Creating a table from a flat file (text file).

chandler — Tue, 09 Mar 2010 18:34:35 GMT

This is a sample of my current code.

filename test_in ftp "'RANV.LSIL.DDMAST.R21.NEWEXTR(0)'"
SYMBOLGEN: Macro variable MVS_ENV resolves to SYS2
6240 host="&mvs_env"
SYMBOLGEN: Macro variable FTPUSER resolves to FTPS099
6240 ! user="&ftpuser"
SYMBOLGEN: Macro variable FTPPASS resolves to {sas001}Mjl0aW1lMDg=
6240 ! pass="&ftppass" ;
6241
6242
6243 data test_curr ;
6244
6245 * if _n_ gt 99 then stop ;
6246
6247 infile acls_in pad missover recfm=f lrecl=10000 ;
6248
6249 *** org2 is bank, org3 is area, org4 is city, org5 is branch ;
6250
6251 input
6252
6253 @1 bknum s370fpd03.
6254 @4 appl s370fpd02.
6255 @6 acct s370fpd10.
6256 @16 source_ind $ebcdic01.
6257 @17 tybrk s370fpd03.
6258 @20 stat s370fzd01.
6259 @21 prcscde $ebcdic01.
6260 @22 org1_in s370fpd03.
6261 @25 org2_in s370fpd03.
6262 @28 org3_in s370fpd03.
6263 @31 org4_in s370fpd03.

Re: PROC SQL: Creating a table from a flat file (text file).

Doc_Duke — Tue, 09 Mar 2010 19:07:36 GMT

In short, No. PROC SQL only works on data that can be associated with a LIBNAME or connected to a database server.

Re: PROC SQL: Creating a table from a flat file (text file).

chandler — Tue, 09 Mar 2010 19:29:29 GMT

Thanks, Doc.

Re: PROC SQL: Creating a table from a flat file (text file).

WillDobson — Mon, 15 Mar 2010 15:14:43 GMT

How about creating a dataset view over your flat file and then manipulating that with SQL?

/**************************************************************************/
/* Create a demo file to play with */
filename flat 'c:\temp\test.dat';
data _NULL_;
file flat;
do i = 1 to 1000000;
j = 'a';
put i j;
end;
run;

/* Method 1 - Traditional data set read, followed by SQL manipulations */
data size_data;
infile flat;
input _a _b $;
run;
proc sql;
create table data2 as
select *
from size_data
;
quit;

/* Method 2 - Proc SQL over a view */
data size_view /view=size_view;
infile flat;
input _a _b $;
run;
proc sql;
create table view2 as
select *
from size_view
;
quit;
/**************************************************************************/

I haven't tested the performance extensivly, but the above test is pretty promising.