Create 1 dataset from sets situated in different directories

[Wouter]

Hi,

I've got a little problem. I want to create 1 dataset from different datasets. No problem normally, but these different datasets (with the same name by the way) are situated in different directories. These directories differ by date (so:
..../2010/08
..../2010/09
..../2010/10
..../2010/11
..../YYYY/MM

Normally, something like this should do the job:

%macro combine;
data test;
set
%do i = 1 %to 2;
file&i;
%end;
run;
%mend;
%combine;

But... I have to deal now with 2 variables (month and year). Besides this, the directory will change also --> I think building a macro around it (to cover the problem of the 2 variables: month & year) will not do the job, because the dataset I want to create ("Test" in this case) will be overwritten everytime a new dataset from another directory is read by the set commando (but on the other hand, I need the set commando...)...?

Anyone any idea??

Thanks in advance!!!

[advoss]

So, let me get this straight. Each folder has a SAS member in it, or does it have a flat file in it? If the latter, you need an infile statement that you can programmatically change using the FILEVAR (or is it FILENAME) option of the INFILE statement. Otherwise, is the SAS member always consistent between the various folders, and how many years and months do you need to read.

Here are two possible solutions for the latter:

%macro spin1;
data test;
set
%do year=2009 %to 2010;
%do month = 1 %to 12;
%let zmonth = %sysfunc(putn(&month,z2.));
"C:\&year\&zmonth\raw.sas7bdat"
%end;
%end;
;
run;
%mend spin1;
%spin2;
%macro spin2;
%let libref=0;
%do year=2009 %to 2010;
%do month = 1 %to 12;
%let zmonth = %sysfunc(putn(&month,z2.));
%let libref = %eval(&libref+1);
libname LIBNAM&libref "C:\&year\&zmonth";
%end;
%end;
%let maxlibref = &libref;
DATA TEST;
SET
%do i=1 %to &maxlibref;
LIBNAM&i..RAW
%end;
;
run;
%do i=1 %to &maxlibref;
LIBNAME LIBNAM&libref clear;
%end;
%mend spin2;
%spin2;

[Wouter]

Hi,

I looks really good, but the first code doesn't do the job. For the second code: what do you mean by "LIBNAM&i..RAW"? They are SAS files (sas7dbat) that needs to be imported.

[ballardw]

If these are existing SAS data sets and they do not have the same name you can use a LIBNAME statement that references all of the directories.
in windows it might look like:

libname mylib ('..../2010/08' '..../2010/09' '..../2010/10' '..../2010/11');

The quotes may not be needed if you have no spaces in the paths, but I use them habitually just in case.

The the set statment might look like:
set
mylib.set1
mylib.set2
mylib.set3
mylib.set4
;

[SPR]

Hello Wouther,

"But... I have to deal now with 2 variables (month and year). Besides this, the directory will change also --> I think building a macro around it (to cover the problem of the 2 variables: month & year) will not do the job, because the dataset I want to create ("Test" in this case) will be overwritten everytime a new dataset from another directory is read by the set commando (but on the other hand, I need the set commando...)...?"

First of all with small change of your code the output dataset will NOT be overwritten:
[pre]
%macro combine;
data test;
set
%do i = 1 %to 2;
file&i
%end;
;
run;
%mend;
%combine
[/pre]
I moved ; from file&i to the place before run. After that a macro loop on year and month will help perfectly.

Sincerely,
SPR

[Wouter]

Hi! Thanks for your time and effort! Unfortunately, this doesn't work. See the code below I'm using now:

%MACRO RUNYYMM2(BEG_YYMM,END_YYMM);
%LET BEG_YY = %EVAL(%SUBSTR(&BEG_YYMM,1,4) * 1);
%LET BEG_MM = %EVAL(%SUBSTR(&BEG_YYMM,5,2) * 1);
%LET END_YY = %EVAL(%SUBSTR(&END_YYMM,1,4) * 1);
%LET END_MM = %EVAL(%SUBSTR(&END_YYMM,5,2) * 1);
*GET ROUNDS OF THE LOOP;
%LET L=%EVAL((&END_YY-&BEG_YY)*12 + (&END_MM - &BEG_MM) + 1);
*GENERATE THE DATE CONSTANTS, THESE ARE GLOBAL VARIABLES;
%DO I = 1 %TO &L;
DATA _NULL_;
/* Ways to generate date MACRO VARIABLES*/
CALL SYMPUT('YYYYMM', PUT(INTNX('MONTH',MDY(&BEG_MM,1,&BEG_YY),&I - 1,'B'),YYMMN6.));
CALL SYMPUT('MMDDYYYY', "'"||PUT(INTNX('MONTH',MDY(&BEG_MM,1,&BEG_YY),&I - 1,'E'),MMDDYYN8.)||"'");
run;
%let Jaar=%SUBSTR(&YYYYMM,1,4);
%let Maand=%SUBSTR(&YYYYMM,5);
*PUT THE DATE CONSTANTS IN THE LOG;
%PUT "YYYYMM IS " &YYYYMM;
%PUT "MMDDYYYY IS " &MMDDYYYY;
%PUT "THIS IS LOOP " &I;
Data test;
set "N:\Report\&jaar\&maand\report_test.sas7bdat";
run;%end;
%mend ;
%RUNYYMM2(201007, 201010);

I had to remove the loop statement out of the latest data statement, otherwise there were 2 loops (one at the beginning and one in the data statement --> resulting in 9 months in stead of 3). But... Using 1 or 2 loops (2 loops with the statement you've given) didn't result in a set statement that didn't overwrites the old file.

Right now, the code runs almost perfect, except all months are overwritten... So I only get the latest month in my dataset in stead of the 3 months. Does anyone know if there's a statement that really concatenates? Or do I need to work with IF statements?

Thanks for the answer!

[SPR]

Hello Wouter,

"Hi! Thanks for your time and effort! Unfortunately, this doesn't work."
What exactly does not work? (I've tested my code it works)

As to your code. If you change the last datastep like this:
[pre]
%if &i = 1 %then %do;
Data test;
set "N:\Report\&jaar\&maand\report_test.sas7bdat";
run;
%end;
%else %do;
Data test;
set Test "N:\Report\&jaar\&maand\report_test.sas7bdat";
run;
%end;
[/pre]
then everything should be OK.
Sincerely,
SPR

[Wouter]

Thanks!! This one's working perfectly!!

[Ksharp]

Hi.
A quick view your code.
I think ' proc append ' + 'force' will suit for your logic.
And there is also short-way which use windows command ' dir c:\*.sas7bdat /s' which will list all the sas7bdat under c:\,then using filename + pipe and data-step to extract the path-name of these sas7dbat files.



Ksharp

[ArtC]

A caveat on the FORCE option in APPEND. The FORCE option will not alter the variable list or the attributes of those variables in the base data set using the PDV of the DATA= data set. Consequently APPEND does not perform a union of the two PDVs as would a SET statement.
[pre]data a;
a='aa';
run;
data b;
a='aaaa'; b='b';
run;
proc append data=b base=a force ;
run;
proc print data=a;
run;[/pre]
Without the FORCE the append will not take place. With the FORCE option the append takes place, however the variable B is not added to the base data set and the value of A is truncated.

[Ksharp]

>("Test" in this case) will be overwritten everytime a new dataset from another directory is read by the set commando (but on the other hand, I need the set commando...)...?


What does you mean? Does SPR'code " set data1 data2 data3 data4 .......;" not work?
If you do not want to use data step, also have an alternative way " proc append " to integrate multi datasets.



Ksharp

[Wouter]

Yes, multiple sets would work, but it depends on the number of months that are selected when running the report, so it's not possible to set multiple data sets beforehand...