topic Stack datasets on top of each other in SAS Enterprise Guide

Stack datasets on top of each other

Sean_OConnor — Fri, 14 Sep 2018 15:19:16 GMT

Folks,

I have 102 datasets which I would like to stack on top of each other.

They are all in the same location and they have a name such as this;

For the first one;

esfs0004_prt2010m01.sas7bdat

esfs0004_prt2018m07.sas7bdat

I would like to set up a macro which will iterate just all datasets first, creating a variable called month which would equal "2010m01" for any observation in the dataset esfs0004_prt2010m01.sas7bdat and would be "2018M07" for any observation in dataset esfs0004_prt2018m07.sas7bdat. When this is done, I would like to create a new dataset which has all the observations stacked on each other.

Sny input would be most welcome.

Kind regards,

Sean

Re: Stack datasets on top of each other

Reeza — Fri, 14 Sep 2018 15:23:33 GMT

@Sean_OConnor wrote:

Folks,

I have 102 datasets which I would like to stack on top of each other.

They are all in the same location and they have a name such as this;

For the first one;

esfs0004_prt2010m01.sas7bdat

esfs0004_prt2018m07.sas7bdat

I would like to set up a macro which will iterate just all datasets first, creating a variable called month which would equal "2010m01" for any observation in the dataset esfs0004_prt2010m01.sas7bdat and would be "2018M07" for any observation in dataset esfs0004_prt2018m07.sas7bdat. When this is done, I would like to create a new dataset which has all the observations stacked on each other.

Sny input would be most welcome.

Kind regards,

Sean

data want;

set libname.esfs004: indsname = source;
dsn=source;

run;

You don't need a macro. See the solution above. The name will partially be in DSN variable though you'll need to extract it from the data set name using string functions I'll leave that as a step for you. If you're having issues please feel free to post back with details.

Re: Stack datasets on top of each other

Sean_OConnor — Fri, 14 Sep 2018 15:28:47 GMT

Hi Reeza,

I'm new to this so having some issues;

Below is an example of what I'm trying and the following error message;

libname a "workfolder";

data want;

set a.esfs004: indsname = source;
dsn=source;



NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24 
25 GOPTIONS ACCESSIBLE;
26 data want;
27 
28 set a.esfs004: indsname = source;
ERROR: The data set list (A.esfs004:) does not contain any members.
29 dsn=source;
30 
31 run;


run;

Re: Stack datasets on top of each other

Reeza — Fri, 14 Sep 2018 15:35:24 GMT

It’s saying the library is empty? What does it look like to you? It’s possible it’s case sensitive but it’s rare that would be the issue. Post your full code and log please.

Re: Stack datasets on top of each other

ballardw — Fri, 14 Sep 2018 15:39:47 GMT

@Sean_OConnor wrote:

Folks,

I have 102 datasets which I would like to stack on top of each other.

They are all in the same location and they have a name such as this;

For the first one;

esfs0004_prt2010m01.sas7bdat

esfs0004_prt2018m07.sas7bdat

I would like to set up a macro which will iterate just all datasets first, creating a variable called month which would equal "2010m01" for any observation in the dataset esfs0004_prt2010m01.sas7bdat and would be "2018M07" for any observation in dataset esfs0004_prt2018m07.sas7bdat. When this is done, I would like to create a new dataset which has all the observations stacked on each other.

Sny input would be most welcome.

Kind regards,

Sean

You would be better off creating an actual date value using year and month as between formats and date functions there are many ways to manipulate or use the date value. If you have a character variable like "2018M07" then you have would essentially have to create a date value if want to select records between, for example, June 2017 and Feb 2018, for any purpose.

data example;
   string= '2018M07';
   date = input(strip(compress(string,'M')),yymmn6.);
   put date=date9. date=yymm. date=yymmn.;
run;

Re: Stack datasets on top of each other

jebjur — Fri, 14 Sep 2018 18:29:41 GMT

Here is a sample program that may do what you want:

/* sample data */

data esfs0004_prt2010m01;
input x y;
cards;
1 2
3 4
;
run;

data esfs0004_prt2018m07;
input x y;
cards;
5 6
7 8
9 0
;
run;

/* create macro variables for the count of data set names in the library,
a list of the data set names to use when stacking the tables, and
each data set name in a separate macro variable */

PROC SQL NOPRINT;
SELECT COUNT(MEMNAME) INTO :CNT FROM DICTIONARY.TABLES WHERE LIBNAME EQ 'WORK';
SELECT DISTINCT(MEMNAME) INTO :MEMLIST SEPARATED BY ' '
FROM DICTIONARY.TABLES WHERE LIBNAME EQ 'WORK';
select DISTINCT(MEMNAME) into :v1 - :v%trim(&cnt)
from DICTIONARY.TABLES WHERE LIBNAME EQ 'WORK';
QUIT;

/* add the new variable to each individual data set */

%macro addvar;

%Do i=1 %to &cnt;

Data &&&v&i;
set &&&v&i;
date=substr("&&&v&i",13);
run;

%end;
%mend;

%addvar;

/* append (stack) all the data sets into one */

data final;
set &memlist;
run;