Import automatically multiple pipe delimited files to different Sas Tables

[Debora1]

Hello,

I am just starting in Sas and i hope my question is not too dumm, but i would like to ask for some help, I am using Sas university edition.  I have been thinking a lot but i do not find a my mistake.

My final objective is to import several pipe delimited text files that are in a folder to sas.

I found this topic: https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docse...

1. I ran the code, however i did not get to import my files below the error and code
   1. Following note: 
      107 %drive(C:\SASUniversityEdition\myfolders\sasuser.v94\relationaltables201603,txt)
      NOTE: In a call to the DOPEN routine, the fileref /folders/myfolders/sasuser.v94/relationaltables201603/ exceeds 8 characters, and
      will be truncated.
      C:\SASUniversityEdition\myfolders\sasuser.v94\relationaltables201603 cannot be open.
      108
      109 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
      121

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=/folders/myfolders/sasuser.v94/relationaltables201603/;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                    
                    
      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
       %if %superq(ext) = %superq(&name) %then %do;                         
          %let cnt=%eval(&cnt+1);       
          %put %qsysfunc(dread(&did,&i));  
          PROC IMPORT DATAFILE="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt
			DBMS=DLM
			OUT=WORK.&OUTFILENAME;
			DELIMITER="|";
			GETNAMES=YES;
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be open.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive(C:\SASUniversityEdition\myfolders\sasuser.v94\relationaltables201603,txt) 

Therefore i found this topic: https://communities.sas.com/t5/SAS-Procedures/Problem-importing-my-CSV-into-SAS-University-Edition/t...

and I ran the test and found my files, therefore I am not sure why isnt sas  finding my files.

data _null_;

infile "/folders/myfolders/sasuser.v94/relationaltables201603/arm_groups.txt" obs=100;

input;

put _infile_;

run;

 

Does anybody, have a clue of what can i try?

Thaaanks!

[Shmuel]

1) you changed line

%let filrf=mydir; 

into 

%let filrf=/folders/myfolders/sasuser.v94/relationaltables201603/;   

thus getting the note:

NOTE: In a call to the DOPEN routine, the fileref /folders/myfolders/sasuser.v94/relationaltables201603/ exceeds 8 characters,
 and will be truncated.

Don't change that line.

2) While invoking macro on line

%drive(C:\SASUniversityEdition\myfolders\sasuser.v94\relationaltables201603,txt) ;

you don't need the root "C:\SASUniversityEdition" - cancel it while using SAS UE.

[Debora1]

Hello Shmuel!

Thaaanks so much! Indeed that worked out perfectly for my location problem! In order to consider shared folder safety issues mentioned below i moved my files to another folder and the location problem is solved.

However the issue that appeared several times was this:

WARNING: Apparent symbolic reference TXT not resolved.

The original code i considered, was based on csv files, so what i did to consider pipe files was:

1. Changed the delimiter
   1. DELIMITER="|";
2. File format to txt
   1. %drive(/folders/myfolders/AACT201603_pipe_delimited/relationaltables201603,txt)

Do you have any other tips on how could i solve it?

Thanks!

[Kurt_Bremser · Accepted solution]

There is an error in the code as presented on the SAS website. This statement

%if %superq(ext) = %superq(&name) %then %do;

needs to be

%if %superq(ext) = %superq(name) %then %do;

(no ampersand in the %superq function)

@ChrisHemedinger could you let this be fixed in https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docse... ?

[ChrisHemedinger]

I've alerted @Sue_SAS who looks after this doc.  Thanks for pointing it out!

[Debora1]

Thanks so much for the support!

[Kurt_Bremser]

Several issues.

First, University Edition CANNOT see your C: drive, it can only see the shared folder from inside the virtual machine. There, it is /folders/myfolders.

Second, the file reference used as first argument to the filename function must be a valid SAS name for a file reference. File references can be a maximum of 8 characters long, must start with a character a-z or underline, and can contain only characters a-z, underlines and digits.

So change the %let to

%let filrf=my_inf;

or similar.

[Kurt_Bremser]

Corrected code:

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=my_inf;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                    
                    
      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
       %if %superq(ext) = %superq(&name) %then %do;                         
          %let cnt=%eval(&cnt+1);       
          %put %qsysfunc(dread(&did,&i));  
          PROC IMPORT DATAFILE="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt
			DBMS=DLM
			OUT=WORK.&OUTFILENAME;
			DELIMITER="|";
			GETNAMES=YES;
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be open.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive(/folders/myfolders/sasuser.v94/relationaltables201603,txt)

[Debora1]

Good afternoon Kurt!

thanks so much for the help! The proposed change fitted as a glove!

I am now trying to understand another point that emerged, because in the end i could not import due to this error that appeared several times:

"WARNING: Apparent symbolic reference TXT not resolved."

The original code i considered, was based on csv files, so what i did to consider pipe files was:

1. Changed the delimiter
   1. DELIMITER="|";
2. Changed file format to txt
   1. %drive(/folders/myfolders/AACT201603_pipe_delimited/relationaltables201603,txt)

Do you have any idea on how could i solve it?

Or perhaps another easier way to import several pipe delimited files in a folder automatically?

[Kurt_Bremser]

The WARNING is caused by the surplus ampersand in the %superq function. In this function, the macro variable name must be used without it. In the later explanation of the code in the same page, the code is correct.

[Tom]

It looks like you are using a macro that was already working, but you changed the macro itself instead of just passing in the path in the call to the macro. Go back to the original macro as it looks like it was originally written properly.

In particular the macro variable FILRF is intended to hold the fileref needed for the FILENAME() and DOPEN() statement. So its value needs to be a valid SAS name of 8 characters or less. The only other requirement is that it not conflict with any other filename you are already using.  If you just set it to nothing then the FILENAME() statement will generate one for you.

For the path make sure to use the path as it is seen by SAS, not how it looks from Windows on your PC.  Looks like you are using SAS University edition so SAS is running on Unix so the path needs to be unix style (ie case sensitive) and start with /folders/myfolders/.

%drive(/folders/myfolders/sasuser.v94/relationaltables201603,txt) 

Personally I NEVER put anything into the SASUSER folder, but perhaps that is what the instructions you are following asked you to do?  Project data should be in project folders.

[Debora1]

Hey! Thanks so much for the advices and explanations!

I moved the file to another folder and the location and given the advices the location problem was solved.

However, i found another problem the files were not imported to my work library, due to this error appearing for several times.

"WARNING: Apparent symbolic reference TXT not resolved."

the post that i am basing on is, uses this code for csv files, so i simply modified the delimiter to pipe and changed the file format.

Do you have any idea what could i try?

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=mydir;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                    
                    
      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
       %if %superq(ext) = %superq(&name) %then %do;                         
          %let cnt=%eval(&cnt+1);       
          %put %qsysfunc(dread(&did,&i));  
          PROC IMPORT DATAFILE="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt
			DBMS=DLM
			OUT=WORK.&OUTFILENAME;
			DELIMITER="|";
			GETNAMES=YES;
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be open.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive(/folders/myfolders/AACT201603_pipe_delimited/relationaltables201603,txt) 

[Tom]

I don't see any code that is attempting to reference a macro variable named txt.

You do have the wrong slash being inserted into the paths in the middle of that macro.

Note that SAS on Windows should automatically convert x/y to x\y when used in a path.  But on Unix \ is used as an escape character so SAS cannot automatically do the reverse.  So if you want to just use one character and run the code on either windows or Unix then use /.

PROC IMPORT DATAFILE="&dir/%qsysfunc(dread(&did,&i))" out=dsn&cnt

[Sue_SAS]

Hey Everyone,

I corrected the document. You will see the change at the next software release. Thanks for letting us know about this error.

Sue_sas