topic Merging sequence data and get distinct date in SAS Enterprise Guide

Merging sequence data and get distinct date

woo — Tue, 22 Sep 2015 18:53:30 GMT

Hello Friends need small help please,

i wrote a code to read kind of similar files from directory and came up with 9 datasets. test1, test2....test9. Now i wants to merge them all using common variable "date" and from that 1 dataset (after merging all in one) wants to get distinct date,

please help.

my below logic is not working

%do i=1 %to _n_iterate.; (where n_iterate is total number of files that i have defined it iearlier)

proc sort data=test&i.; (have datasets from test1 to test9)

by date;

run;

data merge_all;

set test&i.;

by date;

run;

%end;

proc sql;

create table distinct_date as select distinct date from merge_all;

run;

Re: Merging sequence data and get distinct date

ballardw — Tue, 22 Sep 2015 19:09:59 GMT

When something doesn't describe how it does not work: error messages (describe/post the errors), no output, unexpected output.

%do only works within a macro defined within a %macro/%mend structure. And the macro has to be called with the macro name and any parameters.

You probably do not need any macro coding for this. And for the purpose you are showing there is no need to sort the data our use BY;

Data merge_all;

set test1 - test9;

run;

Re: Merging sequence data and get distinct date

Reeza — Tue, 22 Sep 2015 19:34:43 GMT

A set statement with a BY statement does not perform a merge. Generally, SET is used to append (stack datasets) while MERGE is used to merge datasets side by side. It sounds like you want to append. To add to @ballardw solution, I would add a KEEP=DATE to the SET statement.
Your final proc sql to select distinct date appears correct.

Re: Merging sequence data and get distinct date

woo — Tue, 22 Sep 2015 22:43:11 GMT

Thanks Reeza and Ballardw,

My code are in macro only :), just was trying to show you scenario yes i am concatenating it, not merging

But anyways find below solution,

/*sort all datasets*/

%do i=1 %to _n_iterate.; (where n_iterate is total number of files that i have defined it earlier)

proc sort data=test&i.; /*have datasets from test1 to test9*/

by date;

run;

%end;

/*concatenating them */

data merge_all;

set %do k=1 %to &n_iterate.; work.test&k. %end;;

by date;

run;

/*get distinct code*/

proc sql;

create table distinct_date as select distinct date from merge_all;

run;