topic Re: Incorporating Data merge into macro in SAS Programming

Incorporating Data merge into macro

DangIT — Mon, 28 Jul 2014 19:51:32 GMT

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.

Re: Incorporating Data merge into macro

Astounding — Mon, 28 Jul 2014 21:04:46 GMT

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.

Re: Incorporating Data merge into macro

data_null__ — Mon, 28 Jul 2014 22:21:11 GMT

can you use?

data Summary;

merge profile_:;

by ID;

run;

Re: Incorporating Data merge into macro

RW9 — Tue, 29 Jul 2014 09:07:04 GMT

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;

Re: Incorporating Data merge into macro

data_null__ — Tue, 29 Jul 2014 11:21:02 GMT

RW9 wrote:

Great idea if it works, you could also do:

I use it all the time.:smileyshocked:

Re: Incorporating Data merge into macro

DangIT — Tue, 29 Jul 2014 12:57:43 GMT

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

Re: Incorporating Data merge into macro

data_null__ — Tue, 29 Jul 2014 13:02:24 GMT

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.

Re: Incorporating Data merge into macro

DangIT — Tue, 29 Jul 2014 13:08:45 GMT

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