topic Re: Averaging based on string in SAS Programming

Averaging based on string

jhh197 — Wed, 25 Apr 2018 15:12:22 GMT

Hi ,

I have a string

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

Re: Averaging based on string

PeterClemmensen — Wed, 25 Apr 2018 15:20:00 GMT

What if it contains both? Like in your example?

Re: Averaging based on string

novinosrin — Wed, 25 Apr 2018 15:28:10 GMT

%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;

Re: Averaging based on string

Astounding — Wed, 25 Apr 2018 15:37:03 GMT

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;