Assign filename in Proc Import

[SPhill]

Is there any way to assign your file name in proc import, it's easily done in an extra data step but I'd like to avoid the extra datastep if I can? Needless to say I'll have multiple file to import and assign individual names for var 'projclass' in this example.

Thanks

SP

:-

proc import datafile ="C:\MPFS\u226aw.rpt" OUT=sp1 dbms=csv replace;
getnames=yes;
datarow=2;
run;
*;
Data a1;
set sp1;
projclas='U226AW';
run;

 

[ChrisW75]

I'm sorry, I don't understand what you're asking here. Are you basically wanting to run the proc import multiple times on several different files?
If the files are the same format, and all you want is to read them all in, then set up a single variable which contains the filename there are 2 ways to do this.

1 - Use a macro to loop multiple times, reading in each file. If you have a dataset containing the filenames, you could modify this example to do that for you. This particular example was actually used to output multiple files:-

* Get list of agent codes that I'm outputting *;
proc sort data=full_dat(keep=agent) out=agents nodupkey;
  by agent;
run;

* Use array to create list of macro variables agnt1, agnt2 etc *;
*  each one contains an agent number                           *;
data _null_;
  set agents nobs=num_agents end=eof;
  file print;
  array agnt{99} $2;
  retain agnt;

  agnt{_n_} = agent;
  if eof then do;
    call symput('agnt_cnt',num_agents);
    do i = 1 to num_agents;
      rec = compress('agnt' || i);
      call symput(rec,agnt{i});
    end;
  end;
run;


* Macro to output the data I want *;
%macro outpt(o_agnt);
data _null_;
  set full_dat(where=(agent="&o_agnt")); 
  file blah recfm=v lrecl=4000;
  put @1 allrec;
run;
%mend outpt;

* Macro to call output macro with correct agent code *;
*Row by row breakdown:-
  Loop from 1 t limit set by number of different agents
  let macro variable blah equal &agnt1, &agnt2 etc
  call macro outpt and pass it the value of the agent code for this iteration
*;
%macro loop;
  %do i=1 %to &agnt_cnt;
    %let blah = &&agnt&i;
    %outpt(&blah);
  %end;
%mend loop;

%loop;

Another alternative is to write a datastep which reads in the files. you can create a filename statement which actually specifies multiple files:-

filename reps ("&PATH.\Matrix*.txt");

data reps_in;
  LENGTH filid $100
         TM $40;
  infile reps recfm=v lrecl=512 dsd dlm='09'x missover filename=filid;

  length from_locator      $5
         to_locator        $5
         ;

  input from_locator @;

  if from_locator = 'From' then delete;

  input to_locator
        ;

  TM = trim(left(translate(catx(' ',scan(filid,-3,'/\._ '),scan(filid,-2,'/\._ ')),' ','_')));

run;

The filename statement can use wildcards as shown, or it can just include a list of files enclosed individually in quotes and separated by commas.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[SPhill]

Thanks for the swift response and that's useful stuff, but what I have are a list of .csv type files (for our purposes they are called.rpt) which I need to read in and for every .rpt file I need to assign a variable called 'projclas'. Projclas has to match the .rpt filename it's reading in as in the above example it'll be 'u226aw'.

I guess what I'm saying is can you assign a variable to match the name of the file you are reading in whilst in proc import.

Hope I'm making sense here??

SP

(Using PC SAS 8.2)

 

[ChrisW75]

Yup. You are.
Use my second example, change the delimiter to ',' (change "dlm='09'x" to "dlm=','"), and that should pretty much give you what you need.

Give it a try and let me know how you go. I reckon you'll need to add in a couple of other tweaks to get it doing EXACTLY what you need, but it shouldn't be anything complicated.
Proc Import, as far as I know doesn't give you an option to create a variable with the filename in it. You could try going through the manual though...

http://support.sas.com/onlinedoc/913/docMainpage.jsp

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[SPhill]

Yep nearly there thanks, I had found this '

http://support.sas.com/ctx/samples/index.jsp?sid=157&tab=code'

which I've tried, it pretty much works, only problem I have now is I'm only pushing out the final .rpt file in my directory, it reads the other ones in but doesn't seem to hold them out in my 'set' statement which I'm just trying to fix:-

filename blah pipe 'dir C:\Scott\*.rpt /b';
data _null_;
infile blah truncover end=last;
/* Edit length as needed */
length fname $20;
input fname;
i+1;
/* The parsing of FNAME may need to be changed depending on your OS. */
call symput('fname'||trim(left(put(i,8.))),scan(trim(fname),1,'.'));
call symput('pext'||trim(left(put(i,8.))),trim(fname));
if last then call symput('total',trim(left(put(i,8.))));
run;
%macro test;
%do i=1 %to &total;
proc import datafile="c:\Scott\&&pext&i" OUT=&&fname&i dbms=csv replace;
getnames=yes;
datarow=2;
run;

*proc print data=work.&&fname&i (obs=14);;
title &&fname&i;
run;

data sp;
set work.&&fname&i ;;
projclas='projclas';
projclas="&&fname&i";
%end;
%mend;
/* Invoke the macro */
%test;
proc print data=sp (obs=14);
run;

 

[ChrisW75]

I think the problem is the last datastep in your %test macro.
1 - You should finish it with a run statement to make sure it executes within your macro loop.
2 - If you're trying to append all the data together, Proc Append is probably a better bet, at present, in your loop, SP gets over written with each iteration of the loop by the latest dataset read in.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.