BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Debora1
Obsidian | Level 7

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;
 
1.PNG

 

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

 

Thaaanks!

 

1 ACCEPTED SOLUTION
13 REPLIES 13
Shmuel
Garnet | Level 18

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
Obsidian | Level 7

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!

ChrisHemedinger
Community Manager

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

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.
Kurt_Bremser
Super User

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
Super User

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
Obsidian | Level 7

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?

Debora1_0-1587564367600.png

 

 

Kurt_Bremser
Super User

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
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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
SAS Employee

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 13 replies
  • 1959 views
  • 10 likes
  • 6 in conversation