Desktop productivity for business analysts and programmers

Merging sequence data and get distinct date

Reply
Super Contributor
Super Contributor
Posts: 318

Merging sequence data and get distinct date

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;

 

 

 

 

 

 

 

Grand Advisor
Posts: 10,196

Re: Merging sequence data and get distinct date

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;

Grand Advisor
Posts: 17,313

Re: Merging sequence data and get distinct date

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.
Super Contributor
Super Contributor
Posts: 318

Re: Merging sequence data and get distinct date

Thanks Reeza and Ballardw, 

 

My code are in macro only Smiley Happy, 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;

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 249 views
  • 0 likes
  • 3 in conversation