%global string;
%let string=%nrstr(a,123;b,321;c,456;d,789;e,888);
%put "&string.";
options mprint mlogic symbolgen;
%macro test;
%let word_cnt = %eval(%sysfunc(countc(%nrbquote("&string."),%str(';')))+1);;/* Problem in this line */
%let count=%eval(word_cnt);
%put &word_cnt.;
%do i = 1 %to &word_cnt.;
%let var&i=%trim(%qscan(%superq("&string."),&i,%str(';' ))); /* Problem in this line...rest every thing is fine */
%put &&var&i;
%end;
%do j=1 %to &word_cnt.;
%let dataset_key&j= %scan(%quote(&&var&j),1,%str(','));
%let dataset_Name&j= %scan(%quote(&&var&j),2,%str(','));
%put &&dataset_key&j;
%put &&dataset_Name&j;
%end;
% put &string;*/;
%mend test;
Throw this code away. NOW.
Do the calculations in a simple data _null_ step, and use call symput to populate macro variables.
Instead of a string with two sets of delimiters, use a control dataset with two variables as a source.
SAS will resolve the code for you. Just run the code.
If you run into problems, look at the SASLOG to see if you can figure out what the problem is. If you can't figure it out yourself, then post the SASLOG into the {i} window so we can take a look at it.
Your code has been resolved...
Please consult with the guidance below the Post button on how to post a question. Provide test data in the form of a datastep, what the output should look like. You code, explain what it is supposed to do, what it does not do. POst logs if there are warnings/errors you want explained.
What I can tell from your code is that your not thinking the SAS way, but are trying to force your coding through macro, which i not a good idea. Lists of values, loops over those values, multiple datasets etc. are all indicators of a process not thought about.
Problem #1: I suspect that COUNTC is the wrong function. It counts characters. I suspect you need COUNTW instead.
Problem #2: %SUPERQ does not take anything in quotes. It requires the name of a macro variable, in this case (probably):
%superq(string)
It would be a little easier if you provided more of the key information. "Problem here" is helpful, but tell us what actually happens as well.
Hello,
Use a data step instead.
%let string=%nrstr(a,123;b,321;c,456;d,789;e,888);
data _NULL_;
strvar="&string.";
do i=1 to countw(strvar,";");
keyname=scan(strvar,i,";");
call symput(cats("dataset_name",i),scan(keyname,2,","));
call symput(cats("dataset_key",i),scan(keyname,1,","));
end;
run;
%put &dataset_key2.;
%put &dataset_name3.;
Thanks for the help it was really usefull....but i have one small issue in this, I am unable to use -->
%let string=%nrstr(a,123;b,321;c,456;d,789;e,888);
the above line as it is coming from other call symput variable which i am getting from a dataset. So how will I apply
%nrstr there.
I'm not sure i understand your question. Are you saying that the string "a,123;b,321;c,456;d,789;e,888"
is obtained from a variable in another dataset ?
If so, rather than exporting the string with call symput, append the column to your dataset with
a sql join or data merge.
Edit : If all you want is to export the string into macrovariables there is no need for a join, work on the dataset directly :
data _NULL_;
set mydataset; /* dataset containing the string strvar */
do i=1 to countw(strvar,";");
keyname=scan(strvar,i,";");
call symput(cats("dataset_name",i),scan(keyname,2,","));
call symput(cats("dataset_key",i),scan(keyname,1,","));
end;
run;
%put &dataset_key2.;
%put &dataset_name3.;
Note that if your dataset has more than one observation, only macrovariables for the last row will eventually be available.
If you want to create macrovariables for every rows of the dataset, you have to add the row index in the macrovariables names :
call symput(cats("dataset_name",i,"_",_N_),scan(keyname,2,","));
That said, you probably don't need to create all those macrovariables. Tell us want you really want to do and someone will
give you a better option.
To give you good advise:
Tell us a bit more what you start with - like is this string in a single column and row already in Oracle or are you creating the macro variable with the string from multiple rows?
"I have to do changes in SQL only"
Why can't we also use SAS data step? Does all of the processing need to happen in Oracle?
"I don't know how should I append it.i want them as macro variable and need to pass them further in next piece of code."
Can you describe a bit more why this needs to be in macro variables? What is it that you want to do downstream. Using a SAS data step with call execute() can make things often simpler than trying to pack everything into macro logic only.
I gather by this post you are not a SAS programmer? You are mixing up different technologies and creating restrictions for yourself which do not exist:
"I can not do any change in Oracle."
How are you getting the data, is it a text file, is it from pass-through sql?
"I have to do changes in SQL only."
SQL is not SAS nor is it Oracle. SQL is a programming language. It can be done in the database, it can be done in SAS, it can be passed through from SAS. None of this changes the fact that if your programming in SAS, you can use either SQL (provided as proc sql) or Base SAS.
"i want them as macro variable"
Macro is not the place to be storing data. Put data in a dataset.
" need to pass them further in next piece of code"
Datasets can be "passed" onwards, this makes no sense.
I would start by assessing step one in the overall procedure which is what is causing you these problems:
a,123;b,321;c,456;d,789;e,888
Why are you getting this string, how are you getting this. Why is is not a plain text file? If it was a file then a simple datastep import procedure to get the data into a dataset is all that is needed. Then your process will be far simpler and smoother.
Throw this code away. NOW.
Do the calculations in a simple data _null_ step, and use call symput to populate macro variables.
Instead of a string with two sets of delimiters, use a control dataset with two variables as a source.
I got the solution for the same......I thanks everyone of you for your support and guidance.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.