BookmarkSubscribeRSS Feed
JasonNC
Quartz | Level 8

Hi,

I am having  a macro variable

%let group=country state city county

for each iteration i need to remove the last one and use it as a group item

i have to 3 iterations

when i=1

&group=country state city

proc sql ;

creat table test1

select *,sum(pop)

from tes1

group by &group

when i=2

&group=country state

I want create a macro variable like that.i want to use that macro variable in grouping sql.

i tried using %scan but i am duno know how to handle the remaining value of the string.

Can some one help me out.

6 REPLIES 6
RickM
Fluorite | Level 6

Hi Jason,
You can try using the following code.

%let vars=country state city county;

%do i=1 %to 3;

%let group=%substr(&vars,1,%eval(  %index(&vars,  %scan(&vars,%eval(5-&i))  )-1)  );

%put &group;

%end;

The nested functions can be a little hard to follow. Using another %do loop and concatenating the variables(%let group=&group %scan(&vars,&j) ) might be a better option if you want to improve readability of the program.

Good luck!

JasonNC
Quartz | Level 8

Hi RIck,

Thanks for replying.I got it .Anways i did the same way nested code.previously i did a small mistake.

i am trying to insert ,using tranwrd function but it is not working in %sysfunc

%let group1=%sysfunc(tranwrd("&group",' ' , ',');

it is not inserting , between the values.

FloydNevseta
Pyrite | Level 9

Try this:

%let group=country state city;

%let group1=%sysfunc( tranwrd( &group, %str( ),%str(,) ));

%put &group1;

RickM
Fluorite | Level 6

Also

%let group1=%sysfunc( translate( &group, ',' , ' ' ));

works.

Astounding
PROC Star

Jason,

Perhaps it is possible that you are solving this problem the wrong way.  You can get the sums of POP at various levels in one step:

proc summary data=tes1;

   class state county city;

   var pop;

   output out=test1 sum=;

run;

You can use the ID statement to bring in other variables if needed, and another statement that I forget off the top of my head to obtain just some of the levels of summarization instead of all.

At any rate, PROC SUMMARY with a CLASS statement is definitely a tool you should have in your arsenal.

Good lucki.

Ksharp
Super User

How about :

%macro test;


%let group=country state city county;
%put &group;

%let i=%sysfunc(countw(&group));
%let temp=%scan(&group,&i);
%let group=%sysfunc(tranwrd(&group,&temp,%str( )));
%do %while(&i ge 2);
%put &group;
%let i=%eval(&i-1);
%let temp=%scan(&group,&i);
%let group=%sysfunc(tranwrd(&group,&temp,%str( )));
%end;


%mend test;

%test

Ksharp

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
  • 6 replies
  • 1133 views
  • 0 likes
  • 5 in conversation