BookmarkSubscribeRSS Feed
laneylaners
Obsidian | Level 7

I had to switch servers I was using at work, and the new one "doesn't have SAS/CONNECT spawner running on it" per our SAS administrator.  so now i cannot use an old code that had "pipe" in it to import a whole bunch of excel files in one swoop.  they don't have to be appended, but i would like each one saved as it's own dataset.  is there anyway to do this WITHOUT using "pipe"?  everything i've looked for online doesn't have the output i want as an option.  i'm using SAS EG 6.1 (64 bit)

 

old code below

 

 

 

filename indata pipe 'dir C:\Users\engacr\Documents\"My SAS Files"\9.4\200k_Cars\2015_Q1 /b ';
data file_list; length fname $90 in_name out_name $50; infile indata truncover; input fname $ 90.; in_name=translate(scan(fname,1,'.'),'_','-'); out_name=cats('TEMP_',in_name); if upcase(scan(fname,-1,'.'))='XLSX'; run;
data _null_; set file_list end=last; call symputx(cats('dsn',_n_),in_name); call symputx(cats('outdsn',_n_),out_name); if last then call symputx('n',_n_); run; %macro test; %do i=1 %to &n; PROC IMPORT OUT= work.&&outdsn&i. DATAFILE= "C:\Users\engacr\Documents\My SAS Files\9.4\200k_Cars\2015_Q1\&&dsn&i...xlsx" DBMS=EXCEL REPLACE; GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; DBDSOPTS= "DBTYPE=(pct_load='NUM(8)')"; RUN; %end; %mend; %test data work.combined; length veh_type_um $20.; set TEMP_:; run;

 

excel files in folder:

 

DTTX_342349.xlsx

DTTX_389923.xlsx

TTAX_346722.xlsx

TTPX_981123.xlsx

 

and so forth

 

would like them to output as:

 

DTTX_342349.sas7bdat

DTTX_389923.sas7bdat

TTAX_346722.sas7bdat

TTPX_981123.sas7bdat

 

and can append them if necessary

4 REPLIES 4
Reeza
Super User

So the pipe command is used to create the list of files that are to be imported.  If you don't have pipe you'll have to find another way. 

It seems that you can use filename functions for this, but I can't find a good reference. 

 

Here's one example:

 

https://communities.sas.com/t5/SAS-Procedures/Get-directory-listing-on-linux/m-p/209520/highlight/tr...

 

 

laneylaners
Obsidian | Level 7

I'm using SAS EG 6.1 (64 bit)

Kurt_Bremser
Super User

@laneylaners wrote:

I'm using SAS EG 6.1 (64 bit)


Which is not really important in this context. Location and definition of the workspace server is important.

Have you already tried to run your code, and what were the results (log) if you did?

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
  • 4 replies
  • 1395 views
  • 2 likes
  • 3 in conversation