DATA Step, Macro, Functions and more

Kindly Help to resolve code

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Kindly Help to resolve code


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


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 10,574

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Respected Advisor
Posts: 3,278

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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
SAS Super FREQ
Posts: 9,433

Re: Kindly Help to resolve code

Posted in reply to PaigeMiller
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.
Super User
Super User
Posts: 9,840

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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.

Super User
Posts: 6,934

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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.

Super Contributor
Posts: 359

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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.;
Occasional Contributor
Posts: 6

Re: Kindly Help to resolve code

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.

 

Super Contributor
Posts: 359

Re: Kindly Help to resolve code

[ Edited ]
Posted in reply to ankitk321

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.

Occasional Contributor
Posts: 6

Re: Kindly Help to resolve code

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
Respected Advisor
Posts: 4,797

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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

Super User
Super User
Posts: 9,840

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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.

Solution
2 weeks ago
Super User
Posts: 10,574

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 6

Re: Kindly Help to resolve code

Posted in reply to ankitk321

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 156 views
  • 3 likes
  • 8 in conversation