topic Re: Sas macro sas functions in SAS Programming

Sas macro sas functions

khandelwalanmol — Mon, 11 Jan 2021 15:05:49 GMT

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.

Re: Sas macro sas functions

ballardw — Mon, 11 Jan 2021 15:24:42 GMT

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 ".

Re: Sas macro sas functions

khandelwalanmol — Mon, 11 Jan 2021 15:27:44 GMT

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.

Re: Sas macro sas functions

khandelwalanmol — Mon, 11 Jan 2021 15:29:52 GMT

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

Re: Sas macro sas functions

Tom — Mon, 11 Jan 2021 15:33:44 GMT

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

Re: Sas macro sas functions

ballardw — Mon, 11 Jan 2021 15:49:20 GMT

@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".

Re: Sas macro sas functions

khandelwalanmol — Mon, 11 Jan 2021 15:56:15 GMT

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

Re: Sas macro sas functions

ballardw — Mon, 11 Jan 2021 16:25:48 GMT

@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.