Averaging based on string

[jhh197]

Hi ,

I have a string

%let Psv = DISC_CP|DISC_SP|DISC_MI|AMECS_CP|AMECS_SP|AMECS_MI;

So I have a dataset test1 and wanted to create test2 and test3

Test1

MEMID	ITEMID	DISC_CP	DISC_SP	DISC_MI	AMECS_CP	AMECS_SP	AMECS_MI
501489	25689	2.3	1.2	3.3	6.7	2.2	5.6
501489	23287	2.3	1.2	3.3	6.7	2.4	5.6
501489	26625	2.6	1.3	3.3	6.8	2.4	5.3
702569	32589	3.5	1.4	4.2	8.9	1.2	3.1
702569	32291	3.5	1.4	4.2	8.7	1.2	3.1
702569	38896	2.1	1.5	4.2	8.3	1.2	4.9

So if the string &Psv contains  DISC then it should create Test2 dataset which looks like below group by

MEMID AND ITEMID

MEMID	ITEMID	DISC_CP	DISC_SP	DISC_MI
501489	25689	2.3	1.2	3.3
501489	26625	2.6	1.3	3.3

So if the string &Psv contains  AMECS  then it should create Test3  dataset which looks like below group by

MEMID only

MEMID	AMECS_CP	AMECS_SP	AMECS_MI
501489	6.73333	2.3333	5.5
702569	8.63333	1.2	3.7

Can anyone please help

Thanks

[PeterClemmensen]

What if it contains both? Like in your example?

[novinosrin]

%let Psv = DISC_CP|DISC_SP|DISC_MI|AMECS_CP|AMECS_SP|AMECS_MI;
%macro test; 
      %if %index(&Psv,DISC)>0 %then %str(keep=MEMID	ITEMID DISC:);
	  %else %if %index(&Psv,AMECS)>0 %then %str(keep= MEMID	ITEMID AMECS:);
%mend;


data want;
set have(%test);

run;

[Astounding]

There's a lot missing from your question.  For example, what if &PSV contains AMECS_CP but that is the only occurrence of AMECS.  Should AMECS_SP and AMECS_MI be omitted from TEST3?

Rather than try to answer this type of question (and that's not the only one), I'll give you a piece of the answer.  To get TEST3:

proc summary data=have nway;

var AMECS_: ;

class memid;

output out=test3 (drop=_type_ _freq_) mean=;

run;

You may need to modify this slightly, depending on how you further define the requirements. 

If you have control over the incoming macro variable(s), it would be more useful to have two variables (space-delimited):

%let amecs_list = AMECS_CP AMECS_SP AMECS_MI;

%let discs_list = DISC_CP DISC_SP DISC_MI;