Monthly Archives: July 2008

Infile Loop Macro

Posted by on July 25, 2008 No comments

A macro loop to read in several raw data files into a single dataset in a single data step: 

data infiles (keep = filename);  
length filename $128; 
     infile "c:pathtofilecontainingfilenames.txt" pad missover;         
     input filename $; 
run; 

data _null_;     
     dsid = open("infiles");     
     nobs = attrn(dsid,’nobs’);     
     rc   = close(dsid);     
     call symputx("N",nobs); 
run; 

data _null_; 
     set infiles;      
     call symput(‘filename’||trim(left(_N_)),compress(filename)); 
run; 

%macro infileLoop; 
%do fileLoop = 1 %to &N.;  
data foo;          
     infile "c:pathtodirectoryofdatafiles&&filename&infileLoop";            
     [ Rest of program ]          
run; 

proc append base=permanent.dataset data=work_dataset; 
run; 

proc datasets library=work kill memtype=data nolist nodetails; 
run; 
%end; 
%mend infileLoop; 
%infileLoop

A Sample [PROC] SQL Subquery

Posted by on July 25, 2008 No comments

Join two tables by date. If a date in the source table isn’t available in the joining table, join by the NEXT closest available date in the joining AFTER the original date in the source table, i.e. if a date of 01/01/2008 exists in the source table but is not available to join by in the joining table, join by the earliest date next available.

PROC SQL;
CREATE TABLE new_dates as
SELECT date format date7.,
(select min(date)
from FOO.FOO T2
where T2.date >= T1.date) format date7. as new_date
from infiles T1
order by date;
QUIT;