topic Re: Import automatically multiple pipe delimited files to different Sas Tables in SAS Programming

Import automatically multiple pipe delimited files to different Sas Tables

Debora1 — Fri, 17 Apr 2020 11:52:35 GMT

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&docsetVersion=9.4&locale=en

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/td-p/139536

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!

Re: Import automatically multiple pipe delimited files to different Sas Tables

Shmuel — Fri, 17 Apr 2020 12:23:04 GMT

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.

Re: Import automatically multiple pipe delimited files to different Sas Tables

Kurt_Bremser — Fri, 17 Apr 2020 12:27:23 GMT

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.

Re: Import automatically multiple pipe delimited files to different Sas Tables

Kurt_Bremser — Fri, 17 Apr 2020 12:29:17 GMT

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)

Re: Import automatically multiple pipe delimited files to different Sas Tables

Tom — Fri, 17 Apr 2020 12:37:11 GMT

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.

Re: Import automatically multiple pipe delimited files to different Sas Tables

Debora1 — Wed, 22 Apr 2020 13:13:29 GMT

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)

Re: Import automatically multiple pipe delimited files to different Sas Tables

Tom — Wed, 22 Apr 2020 13:22:12 GMT

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

Re: Import automatically multiple pipe delimited files to different Sas Tables

Debora1 — Wed, 22 Apr 2020 13:24:47 GMT

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:

Changed the delimiter
1. DELIMITER="|";
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!

Re: Import automatically multiple pipe delimited files to different Sas Tables

Kurt_Bremser — Wed, 22 Apr 2020 14:05:23 GMT

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&docsetVersion=9.4&locale=en ?

Re: Import automatically multiple pipe delimited files to different Sas Tables

Debora1 — Wed, 22 Apr 2020 14:06:38 GMT

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:

Changed the delimiter
1. DELIMITER="|";
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?

Re: Import automatically multiple pipe delimited files to different Sas Tables

Kurt_Bremser — Wed, 22 Apr 2020 14:08:48 GMT

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.

Re: Import automatically multiple pipe delimited files to different Sas Tables

ChrisHemedinger — Wed, 22 Apr 2020 14:39:48 GMT

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

Re: Import automatically multiple pipe delimited files to different Sas Tables

Sue_SAS — Wed, 22 Apr 2020 21:40:55 GMT

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

Re: Import automatically multiple pipe delimited files to different Sas Tables

Debora1 — Fri, 24 Apr 2020 00:57:39 GMT

Thanks so much for the support!