This robin chick chirped away in my backyard on a glorious summer afternoon. While papa robin hovered above, the chick hopped on ground following Dad. Soon Dad disappeared after leading the chick to a tree where two gray squirrels smacked their lips at the sight of an easy lunch.  Lifting it carefully with a cloth, lest its parents abandon it, I made a nest of dried grass and kept vigil shooing away any squirrels that thought lunch was just a chick away. Granted it was the sweetest two hours of babysitting, still what’s the world coming to? With modern parents going for a drink while leaving their children in the hands of perfect strangers:) Look at my photo of the chick ready to soar.

Thinking back to that absolutely amazing Sunday afternoon, it reminds me of how we teach students at SAS. We hold your hand as necessary. We let you fly when you want to.

In the recent SQL1 workshop I taught in Lacombe, a small town in Alberta, experienced students entered the SQL course debating whether their investment in the post-course workshop would do them any good.

Soon after, a student excitedly shared his Eureka moment and the value he got from the workshop.

From thousands of sub-folders, he needed to import data from thousands of CSV files. He had written extensive code to discover file names and location.  He was thrilled to discover that there was a way to grab the working folder from SAS without having to write complicated code. Note: the working folder is the path you see at the bottom of your screen in your SAS session in the display manager.

Student code to find location of external files:

/* [1] find path*/
%macro getPath(OUTNAME);
 %let rc=%sysfunc(libname(_,.));
 %let local = %sysfunc(pathname(_));
 %let rc=%sysfunc(libname(_));
data &OUTNAME.;rc = "&local.\"; ln = length(rc);run;  
%MEND;
 
%GETPATH(OUTNAME = LOCATE);
 
/*[2] find folder (some limitation; [there are no space in the path ] )*/
 
%let itdir = c:\;
 
filename indircrt pipe "dir &ITdir. /tc  /s";
data xb1;
infile indircrt length = reclen; input @; file print; file print;
if index( _infile_,"/");
informat f1 $10. f2 $5. f3 $2. f4 $16. f5 $96. ;
input f1 f2 f3 f4 f5  &;
run; 
data xb2;retain dirname dir_cr_date dir_cr_time;
set xb1; if f5="" then delete; if f5=".." then delete;if f4 ^="<DIR>" then delete; 
dir_cr_date = input(f1,mmddyy10.); dirname = f5;
dir_cr_time = input(f2||f3, time8.);format dir_cr_date mmddyy10. dir_cr_time timeampm.;
*keep dirname dir_cr_date dir_cr_time;
run; 
proc sort data = xb2 nodupkey; 
by dirname; 
run; 
 
 
/*[3] find file name (Limitation: [there are no space in the path])*/
 
%let fpath = c:\test\;
 
filename incrt pipe "dir &fpath. /tc /a:-d /4";
data db2 ;retain fname cr_date cr_time bit;
infile incrt length=reclen ;input @ ; file print; 
if index( _infile_ , '/' ) ;
if index( _infile_ , '<' ) then delete;
informat cr_date mmddyy10. timet $5. ampm $2.   bit comma15. fName $96.;
input cr_date  timet  ampm    bit  fName &  ;
cr_time = input(timet||ampm, time8.);format cr_date mmddyy10. cr_time timeampm.;
*keep fname cr_date cr_time bit;
run ;
proc sort; 
by fname; 
run;

The student was blown away by the techniques he practiced using the workbook during the workshop. Just a few lines of code and so powerful!! Check my share and the following code that he’s now learned to use to figure out his working folder.

My approach using PROC SQL dictionary tables stores the file name and path in a macro for reuse:

options symbolgen;
%macro CurrDir;
filename _temp '.';
%global Current;
proc sql ;
select xpath into :Current TRIMMED
from dictionary.extfiles
where fileref = '_TEMP';
quit;
filename _temp clear;
%put _user_;
%mend;
/* no ending semicolon on macro calls!!! */
%currdir

Do you like this technique using dictionary tables? You can do so much more. See here for more valuable PROC SQL dictionary tables tips. Considering attending a similar SAS workshop? I’d love to hear from you.

This fledgling robin chick is probably well on its migratory flight south of the border together with veteran American robins. If you’d like to learn to fly with us, here’s a link to the SQL course.