BookmarkSubscribeRSS Feed
jhh197
Pyrite | Level 9

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

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

What if it contains both? Like in your example?

novinosrin
Tourmaline | Level 20
%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
PROC Star

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 621 views
  • 1 like
  • 4 in conversation