DATA Step, Macro, Functions and more

Averaging based on string

Reply
Contributor
Posts: 40

Averaging based on string

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

MEMIDITEMIDDISC_CPDISC_SPDISC_MIAMECS_CPAMECS_SPAMECS_MI
501489256892.31.23.36.72.25.6
501489232872.31.23.36.72.45.6
501489266252.61.33.36.82.45.3
702569325893.51.44.28.91.23.1
702569322913.51.44.28.71.23.1
702569388962.11.54.28.31.24.9

 

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

MEMID AND ITEMID

 

MEMIDITEMIDDISC_CPDISC_SPDISC_MI
501489256892.31.23.3
501489266252.61.3

3.3

 

 

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

MEMID only

MEMIDAMECS_CPAMECS_SPAMECS_MI
5014896.733332.33335.5
7025698.633331.23.7

 

Can anyone please help

 

Thanks

PROC Star
Posts: 1,218

Re: Averaging based on string

What if it contains both? Like in your example?

PROC Star
Posts: 1,593

Re: Averaging based on string

%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;
Super User
Posts: 6,638

Re: Averaging based on string

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;

 

Ask a Question
Discussion stats
  • 3 replies
  • 98 views
  • 1 like
  • 4 in conversation