topic Re: Looping through Files to Import SAS Data Sets in SAS Programming

Looping through Files to Import SAS Data Sets

ErinKSimmons — Tue, 28 Jul 2020 11:32:00 GMT

I am hoping to get code that will loop through the past 49 months of data and import the SAS dataset saved there and then append all 49 together with a new column for the date from the file name.

DATA _NULL_;
DO i = 1 to 49;
DATA _NULL_;
MP1DB = INTNX('month',today(),-i,'B');
CALL SYMPUTX ('CURRENTMONTH',CATS(SUBSTR(PUT(MP1DB,yymmn6.),1,4), '-',SUBSTR(PUT(MP1DB,yymmn6.),5,2)));
RUN;

libname wm "\\SFLC-SAS-AP\pod-data3\MDR\&CURRENTMONTH.\ValueOfInventory\";

data work.oh_di_all_MPi;
set wm.oh_di_all_:;
month=&CURRENTMONTH.;
run;
END;
RUN;

%macro append;
data work.oh_di_all;
set
%do i = 1 %to 49;
oh_di_all_MP&i
%end;
;
run;
%mend append;

%append;

Re: Looping through Files to Import SAS Data Sets

yabwon — Tue, 28 Jul 2020 11:48:13 GMT

Hi,

Maybe something like that:

options mprint;
%macro loop(end);

%DO i = 1 %to &end.;
%let CURRENTMONTH = %sysfunc(INTNX(month, %sysfunc(today()), -&i, B), yymmd7.);

libname wm  "\\SFLC-SAS-AP\pod-data3\MDR\&CURRENTMONTH.\ValueOfInventory\";

data oh_di_all_MP&i;
  set wm.oh_di_all_:;
  where month = "&CURRENTMONTH.";
run;
%END;
%mend loop;
%loop(49)

data work.oh_di_all;
  set oh_di_all_MP1 -- oh_di_all_MP49
  ;
run;

I assume that month is a character variable since you wrote ```month = &CURRENTMONTH.``` and CURRENTMONTH contains a hyphen(-).

Bart

Re: Looping through Files to Import SAS Data Sets

Kurt_Bremser — Tue, 28 Jul 2020 11:56:23 GMT

Do it in a single DATA step:

%macro do_all;

%do i = -49 %to -1;
  %let cm = %sysfunc(intnx(month,%sysfunc(today()),&i),yymmd7.);
  libname i&cm. "\\SFLC-SAS-AP\pod-data3\MDR\&cm.\ValueOfInventory\";
%end;

data work.oh_di_all;
set
%do i = -49 %to -1;
  %let cm = %sysfunc(intnx(month,%sysfunc(today()),&i),yymmd7.);
  i&cm..oh_di_all_:
%end;
;
run;

%do i = -49 %to -1;
  %let cm = %sysfunc(intnx(month,%sysfunc(today()),&i),yymmd7.);
  libname i&cm. clear;
%end;

%mend;

%do_all

Re: Looping through Files to Import SAS Data Sets

Tom — Tue, 28 Jul 2020 12:04:21 GMT

You don't "import" a SAS dataset. You just access it.

What is the pattern in the names of the files? Looks like the one level of the directory is in the month as YYYY-MM?

Do you really want to combine multiple datasets from each of those directories? Or is there just one dataset and you are using the : because the end of the dataset name varies? If the later how does it vary?

It also looks like you are trying to add a variable that indicates which directory the data came from. Is that really needed? Don't the dataset already have a variable with this information?

Re: Looping through Files to Import SAS Data Sets

ErinKSimmons — Tue, 28 Jul 2020 12:17:50 GMT

Each month we pull a snapshot of the Value of Inventory. We try to run this the first of the month, but if that falls on a weekend, we do it the following Monday. This is only one of several monthly files we output.

The files all follow the name pattern OH_DI_ALL_DDMONYYY where DDMONYYYY is the date the data was run since it won't necessarily be the first of the month.

The only thing that changes in the directory name is the month portion. There is a subfolder ValueofInventory in each month's subfolder.

I want to access one SAS dataset from each month within the ValueofInventory subfolder in that month folder. I am using the : because I want to go into 49 month folders into the ValueofInventory subfolder and access the one SAS dataset, whose subscript date (DDMONYYYY) may vary.

Yes I want to add the variable to indicate what directory it comes from because it was a snapshot in time and never originally intended to be concatenated but rather use just one file at a time. Now they want to append to track a historical value from all of these different snapshots so I have to create this variable to do the time series graphing.

Re: Looping through Files to Import SAS Data Sets

Kurt_Bremser — Tue, 28 Jul 2020 12:26:40 GMT

Expand the data step from my previous code to get the month:

data work.oh_di_all;
set
%do i = -49 %to -1;
  %let cm = %sysfunc(intnx(month,%sysfunc(today()),&i),yymmd7.);
  i&cm..oh_di_all_:
%end;
  indsname=ds
;
month = substr(scan(ds,1,'.'),2);
run;

Re: Looping through Files to Import SAS Data Sets

Tom — Tue, 28 Jul 2020 13:01:44 GMT

I would just avoid macro for this as working with so many files is probably easier with data steps.

So first generate a libref for each of your source directories. Let's name the librefs as ZZYYYYMM.

data librefs ;
  length libref $8 rc date 8 month $7 path $256 ;
  today=today();
  format today date date9.;
  do offset=-49 to -1 
    date = intnx('month',today,offset,'b');
    month = put(date,yymmd7.);
    libref=cats('ZZ',put(date,yymmn6.));
    path=catx('\','\\SFLC-SAS-AP\pod-data3\MDR',month,'ValueOfInventory');
    rc=libname(libref,path);
  end;
run;

Then find the actual datasets you want to read by querying the metadata.

proc sql ;
  create table datasets as 
    select b.libref,b.memname,a.date,a.month,a.path
    from librefs a
    inner join dictionary.members b
    on a.libref = b.libref 
    and b.memname like 'OH^_DI^_ALL^_%' escape '^'
    order by b.libref,b.memname 
  ;
quit;

Now you can use a data step to generate a SET statement that lists each actual dataset found.

filename code temp;
data _null_;
  set datasets end=eof;
  file code lrecl=70 ;
  if _n_=1 then put 'set ' @;
  put  libname +(-1) '.' memname @;
  if eof then put ' indsname=indsname ;' ;
run;

And then use that SET statement in a data step. You can pull out the MONTH and actual DATE from the LIBNAME and MEMNAME part of the dataset being read.

data oh_di_all ;
  length indsname $41 month $7 date 8 ;
%include temp / source2;
  month=substr(scan(indsname,1,'.'),3);
  date = input(substr(indsname,length(indsname)-8),date9.);
  format date date9.;
run;