BookmarkSubscribeRSS Feed
chandler
Fluorite | Level 6
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?
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
chandler
Fluorite | Level 6
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.
Doc_Duke
Rhodochrosite | Level 12
In short, No. PROC SQL only works on data that can be associated with a LIBNAME or connected to a database server.
WillDobson
Calcite | Level 5
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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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