topic Re: Combining multiple data sets in SAS Data Management

Combining multiple data sets

Rasheed — Sat, 04 Jul 2015 06:45:34 GMT

I have multiple data sets named

dsnAC16 dsnAC17 dsnAC18 dsnAC19 dsnAC110.........dsnAC125

dsnAC26 dsnAC27 dsnAC28 dsnAC29 dsnAC210.........dsnAC225

dsnAC56 dsnAC57 dsnAC58 dsnAC59 dsnAC510.........dsnAC525

I want to combine all above data set and I am using following code but getting some errors pls guide

%let i=5; %let pat=20;

data combineAC;

set

do %let k = 1 to &i;

do %let h= %eval(1+&i) to %eval(&pats+&i);

dsnAC&k&h

end;

run;

Re: Combining multiple data sets

mingfeng07 — Sat, 04 Jul 2015 08:19:18 GMT

You can try this.

%macro test;

%do i=1 %to 5;

%do j=6 %to 25;

dsnAC&i.&j

%end;

%mend;

data combineAC;

set %test;

run;

Re: Combining multiple data sets

Rasheed — Sat, 04 Jul 2015 11:50:21 GMT

Thanks for your reply

actually I have already complete this program through macro as you mentioned but the problem is that I want to do this without macro

I am working on simulation and want to avoid macro loop for efficient simulation that can run faster

Re: Combining multiple data sets

Reeza — Sat, 04 Jul 2015 14:05:44 GMT

Assuming all datasets start with dsnAc and you don't have other datasets that start with the letter DSNAC?

Otherwise create a macro list by querying SASHELP.VTABLE or Dictionary.Table

data want;

set dsnaC: ;

run;

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 04:52:58 GMT

I don't have any other data set except mentioned above data sets list starting with dsnAC

I want to do this task without using macro loop kindly help me in this regard I would be grateful if you could mention the code for it

Re: Combining multiple data sets

LinusH — Sun, 05 Jul 2015 07:06:49 GMT

Why not a macro?A And Reezas sugestion is not a macro program but the use of a macro variable.

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 07:33:35 GMT

I am working on simulation program in which I don't want to use macro loop because of time saving

I would be grateful if you could mention code because i didn't understand reeza suggestion

Ragards

Re: Combining multiple data sets

Tom — Sun, 05 Jul 2015 11:53:09 GMT

Use of macro will not impact the speed of execution.

A macro just generates code. It takes almost no time at all.

It is the code that will impact the speed.

For an optimal method to code your simulation you probably need to give us more information on what you actually want to do.

Re: Combining multiple data sets

Reeza — Sun, 05 Jul 2015 17:06:40 GMT

Did you try the code in my first answer, mainly using the colon after the name? That appends all datasets that starts with DSNAC. If you need the dataset name you can use the INDSNAME option as well.

data want;
set dsnaC:  INDSNAME=SOURCE;
DNAME=SOURCE;
run;

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 18:31:04 GMT

Thank you very much for your reply

Actually I am simulating 500 samples from multivariate normal distribution under the replicated crossover design. This design is very complicated though. Then for each simulated sample I have to run 2000 bootstrap samples. In all this task is very complicated. I generated 500 simulations and in between i need to combine some data sets I used macro do loop that takes hours of hours to perform this task. Than I red an article of Rick Wicklin about efficient simulation. Where I found that macro do loop should be avoided to make simulation run faster. That is why I am trying following code that will combine multiple data set without macro do loop but I am getting error in it

Data sets need to combine are

DsnAC16 DsnAC17 DsnAC18 DsnAC19 ...................DsnAC125

DsnAC26 DsnAC27 DsnAC28 DsnAC29 ...................DsnAC225

......

DsnAC56 DsnAC57 DsnAC8 DsnAC59 ...................DsnAC525

Code:

%let i=5; %let pat=20; data combineAC; set do k= 1 %to &i; do h= (1+&i) %to (&pat+&i); dsnAC&k&h end; end;

;

run;

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 18:35:41 GMT

Thank you very much for your reply could pls mention complete code for it. Actually I have multiple data sets

DsnAC16 DsnAC17 DsnAC18 DsnAC19 ...................DsnAC125

DsnAC26 DsnAC27 DsnAC28 DsnAC29 ...................DsnAC225

......

DsnAC56 DsnAC57 DsnAC8 DsnAC59 ...................DsnAC525

And I want to combine all without macro do loop that is why I am using following code but getting some error in it

%let i=5; %let pat=20;

data combineAC;

set

do k= 1 %to &i;

do h= (1+&i) %to (&pat+&i);

dsnAC&k&h

end;

;

run;

Re: Combining multiple data sets

Reeza — Sun, 05 Jul 2015 18:41:37 GMT

THAT IS THE COMPLETE CODE.

THE COLON IS A SAS SHORTCUT THAT TELLS IT TO SET/APPEND ALL DATASETS THAT START WITH DSNAC.

HAVE YOU TRIED IT AND IT DID NOT WORK?

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 18:46:54 GMT

Actually the data sets which I want to combine are not in series they are like

DsnAC16 DsnAC17......DsnAC125

then

DsnAC26 DsnAC27.....DsnAC225

...

DsnAC56 DsnAC57.....DsnAC525

So how could I used colon ? for this

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 18:49:46 GMT

I did the same task using macro do loop using following code and its work fine

%macro allmergeAC (a,b); data combineAC; set %do k= 1 %to &a; %do h= (1+&a) %to (&b+&a); dsnAC&k&h %end; %end; ; run;

But I want to do this without macro do loop

%mend;

%allmergeAC (&i,&pat) ;

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 18:54:05 GMT

Sorry for incorrect formatting of previous message

I did the same task using macro do loop using following code and its work fine

%macro allmergeAC (a,b);

data combineAC;

set

%do k= 1 %to &a;

%do h= (1+&a) %to (&b+&a);

dsnAC&k&h

%end;

;

run;

%mend;

%allmergeAC (&i,&pat) ;

But I want to do this without macro do loop

Re: Combining multiple data sets

Reeza — Sun, 05 Jul 2015 19:26:31 GMT

Did you try the code?

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 19:46:09 GMT

Which code ? this one

data want;

set dsnaC: INDSNAME=SOURCE;

DNAME=SOURCE;

run;

No I didn't

Because

I don't understand INDSNAME=SOURCE and DNAME=SOURCE;

How could I use ?

Re: Combining multiple data sets

Reeza — Sun, 05 Jul 2015 20:10:01 GMT

The colon after then prefix dsnac tells SAS to append all datasets that start with the prefix.

The indsname option tells sas to capture the name of the dataset where the observation comes from. This variable isn't stored automatically so the next line DName=source tells SAS to explicitly keep the name.

This way you know which observations come from which data set. These statements are optional, my initial code with only the colon would exclude these options.

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 20:19:56 GMT

Actually the data sets which I want to combine are not in series they are like

DsnAC16 DsnAC17......DsnAC125

then

DsnAC26 DsnAC27.....DsnAC225

...

DsnAC56 DsnAC57.....DsnAC525

So how could I used colon ? for this

And I no need to know which observation is coming from which data set

I just want to combine all data set thats it

Re: Combining multiple data sets

Rasheed — Sun, 05 Jul 2015 20:21:24 GMT

I have to use two do loops to combine these set of data sets but I am unable to write this code