BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ankitk321
Fluorite | Level 6


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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Cynthia_sas
SAS Super FREQ
Hi: In addition to @PaigeMiller's suggestion, I suspect you are getting errors in the log or undesirable output because of possibly "overprotecting" your macro variables with superq and nrbquote. Not sure why you need those.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Astounding
PROC Star

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.

gamotte
Rhodochrosite | Level 12

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.;
ankitk321
Fluorite | Level 6

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.

 

gamotte
Rhodochrosite | Level 12

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.

ankitk321
Fluorite | Level 6
These are the dataset name and key , I am getting from Oracle and it was written in such a way in a coloumn in Oracle table. I have to fetch them from SQL and then break them in dataset and key.

I can not do any change in Oracle. I have to do changes in SQL only.

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.

Looking forward for your help.

Regards
Ankit
Patrick
Opal | Level 21

@ankitk321

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

ankitk321
Fluorite | Level 6

I got the solution for the same......I thanks everyone of you for your support and guidance.

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
  • 12 replies
  • 1292 views
  • 3 likes
  • 8 in conversation