DATA Step, Macro, Functions and more

Incorporating Data merge into macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Incorporating Data merge into macro

Hi,

I currently have a  macro set up that 'profiles' my customers - %Profile(&Group)

The macro runs through several datastep procedures and essentially creates a standard output data set named Profile_&Group.

If I have 3 profiles to create I would run the macro 3 times for example:

%Profile(HighValue) ; (output: Profile_HighValue)

%Profile(MedValue) ; (output: Profile_MedValue)

%Profile(LowValue) ; (output: Profile_LowValue)

After the macro is complete I have a separate code I would run manually to merge the output datasets into a summary table:

Data Profile_Summary ; Merge Profile_HighValue Profile_MedValue Profile_LowValue ;

by ID;

run ;

Sometimes I would only have 2 groups (High,Med) or possibly 5+ groups to profile and merge to one single dataset.

My question:

What is the best way to incorporate my datastep to merge the profile tables together within the macro?

Thanks for your help.

KD


Accepted Solutions
Solution
‎07-28-2014 05:04 PM
Super User
Posts: 5,498

Re: Incorporating Data merge into macro

You could write a new macro that would control all the processing.  One example:

%macro control (mylist);

   %local i next_name;

   %do i=1 %to %sysfunc(countw(&mylist));

         %let next_name = %scan(&mylist, &i);

         %profile (&next_name)

   %end;

   data profile_summary;

   merge %do i=1 %to %sysfunc(countw(&mylist));

      %let next_name = %scan(&my_list, &i);

      profile_&next_name

   %end;

   ;

   by ID;

   run;

%mend control;

%control (HighValue LowValue MedValue)

This is untested code, so you may find a tweak is in order.  But it sounds like the program form is exactly what you are asking for.  Good luck.

View solution in original post


All Replies
Solution
‎07-28-2014 05:04 PM
Super User
Posts: 5,498

Re: Incorporating Data merge into macro

You could write a new macro that would control all the processing.  One example:

%macro control (mylist);

   %local i next_name;

   %do i=1 %to %sysfunc(countw(&mylist));

         %let next_name = %scan(&mylist, &i);

         %profile (&next_name)

   %end;

   data profile_summary;

   merge %do i=1 %to %sysfunc(countw(&mylist));

      %let next_name = %scan(&my_list, &i);

      profile_&next_name

   %end;

   ;

   by ID;

   run;

%mend control;

%control (HighValue LowValue MedValue)

This is untested code, so you may find a tweak is in order.  But it sounds like the program form is exactly what you are asking for.  Good luck.

Frequent Contributor
Posts: 81

Re: Incorporating Data merge into macro

Posted in reply to Astounding

Thank you Astounding, this worked perfectly (just had one typo in above code).

Respected Advisor
Posts: 3,799

Re: Incorporating Data merge into macro

can you use?

data Summary;

     merge profile_:;

     by ID;

     run;

Super User
Super User
Posts: 7,942

Re: Incorporating Data merge into macro

Posted in reply to data_null__

Great idea if it works, you could also do:

data _null_;

     set sashelp.vtables (where=(libname="WORK" and index(MEMNAME,"PROFILE")>0);

     by libname;

     if first.libname then call execute('data profile_summary; merge ');

     call execute(" "||strip(memname));

     if last.libname then call execute('; by id; run;');

run;

Respected Advisor
Posts: 3,799

Re: Incorporating Data merge into macro

RW9 wrote:

Great idea if it works, you could also do:

I use it all the time.:smileyshocked:

Frequent Contributor
Posts: 81

Re: Incorporating Data merge into macro

Posted in reply to data_null__

How did you make that work?? I don't think SAS recognizes the the colon for datasets.

Respected Advisor
Posts: 3,799

Re: Incorporating Data merge into macro

23         data class_1 class_2 Class_3;

24            set sashelp.class;

25            run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: The data set WORK.CLASS_1 has 19 observations and 5 variables.

NOTE: The data set WORK.CLASS_2 has 19 observations and 5 variables.

NOTE: The data set WORK.CLASS_3 has 19 observations and 5 variables.

NOTE: DATA statement used (Total process time):

      real time           0.04 seconds

      cpu time            0.02 seconds

     

26         data class;

27            merge class_:;

28            by name;

29            run;

NOTE: There were 19 observations read from the data set WORK.CLASS_1.

NOTE: There were 19 observations read from the data set WORK.CLASS_2.

NOTE: There were 19 observations read from the data set WORK.CLASS_3.

NOTE: The data set WORK.CLASS has 19 observations and 5 variables.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 327 views
  • 5 likes
  • 4 in conversation