BookmarkSubscribeRSS Feed
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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;

 

 

 

 

 

 

 

3 REPLIES 3
ballardw
Super User

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;

Reeza
Super User
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.
woo
Lapis Lazuli | Level 10 woo
Lapis Lazuli | Level 10

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 981 views
  • 0 likes
  • 3 in conversation