Sas macro sas functions

[khandelwalanmol]

I have a list of names fetched from a table into a ',' separated macro.
Cities='Mumbai,Delhi,jaipur,chandigarh,chennai,goa'
I want to split this inti two macro like
Cities1="Mumbai,delhi,jaipur"
Cities2="chandigarh,chennai,goa"

I am new to learning sas.

[ballardw]

What is the rule for the split?

At the third value? At a specific name? Something else not shown?

And show the code that you are currently using to "fetch from a table into a ',' separated macro ".

[khandelwalanmol]

Rule is i have to keep 4 variables in the first variable and next 4 in another.
If in future the count of cities increase third variable shoukd be created.

[Tom]

---

@khandelwalanmol wrote:
Rule is i have to keep 4 variables in the first variable and next 4 in another.
If in future the count of cities increase third variable shoukd be created.

---

You will want to switch to using a data step from trying to use SQL for this.  So if you have variable named CITY in a dataset named HAVE you can use code like this to create macro variables MVAR1, MVAR2, ... and MVAR_COUNT. 

data _null_;
do i=1 to 4 until(eof) ;
   set have end=eof ;
   length string $500 ;
   string = catx(',',string,city);
end;
  call symputx(cats('mvar',_n_),string);
  if eof then call symputx('mvar_count',_n_);
run;

[ballardw]

---

@khandelwalanmol wrote:
Rule is i have to keep 4 variables in the first variable and next 4 in another.
If in future the count of cities increase third variable shoukd be created.

---

Please check either your counting or your definition. The values you showed look like you start with 6 and want 3 value in each, not 4. So I am not sure how to interpret this "Rule".

[khandelwalanmol]

Sorry for that typo.
It is 3 cities in one variable.

[ballardw]

---

@khandelwalanmol wrote:
Sorry for that typo.
It is 3 cities in one variable.

---

Then that looks like @Tom's solution starting with a "Do i=1 to 3" instead of the "to 4" should work.

[khandelwalanmol]

I am using
Proc sql;
Select distinct cities into :cities separated by ',' from states;
Quit;