How to create a table to list macro N by using proc sql?

Accepted Solution Solved
Reply
Super Contributor
Posts: 309
Accepted Solution

How to create a table to list macro N by using proc sql?

[ Edited ]

Hello:

 

I have the example program to create a Nn macro by proc sql.   Is there a way to create a table to list all of the Nn?  I could find the N1-N500 from 'Results' individually.  However, I would like to review the table from SAS work library.  Thanks.

 

 

data have;

informat name $80.;

input name &;

cards;

John If_True kary

John If_True Mary

John If_True kary

John If_True kary

Tom If_Not Carol

Tom If_Not Carol

Tom If_Not Carol

Joe If_True Jane

Joe If_False Jane

Joe If_False Paul

Joe If_False Jane

Paul If_False Jane

Joe If_False Jane

Joe If_False Jane

;

proc SQL;

SELECT

sum(case when find (name,'If_True')>0 and index(Name,'kary')>0 then 1 else 0 end) into: N1 FROM have;

SELECT

sum(case when find (name,'If_False')>0 and index(Name,'Paul')>0 then 1 else 0 end) into: N2 FROM have;

quit;

 

%put &n1;

%put &n2;

 

I would like the table showing the result is

N1 3

N2 2

 

or

N1  N2

3   2

 

 


Accepted Solutions
Solution
‎06-28-2017 09:01 AM
Super User
Posts: 11,130

Re: How to create a table to list macro N by using proc sql?

The %put function has options to list the macro variables in a current scope:

 

%put _user_;

will display ALL user defined variables

 

 

Putting much data into macro variables is often a path to madness.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,789

Re: How to create a table to list macro N by using proc sql?

[ Edited ]

Can you please show us ( a portion of) data set HAVE?


Can you please show us (a portion of) the end result you would like to have?

 

Why, if you want the end result to be a table, are you putting the values into MACRO variables? You can't easily make a table of macro variables.

 

 

Super User
Super User
Posts: 7,720

Re: How to create a table to list macro N by using proc sql?

Please follow the guidance at the create a new question page, just below Post.  Post example test data in the form of a datastep, what the output should look like.  Also, please refer to the other questions you have posted here - almost none of them have been marked Correct.

 

If you want a list you use into with the separated by clause, think I provided this before.  However with test data and what you want to do there is other solutions - again as I provided before.

Super User
Posts: 7,431

Re: How to create a table to list macro N by using proc sql?

If you want a list of results, store it in a dataset, not in macro variables. Datasets are WAY easier to handle.

Since what you want is just a count for certain strings, you should first create a table of said strings.

Depending on the substrings you're looking for (eg if they are always single words), the whole exercise could end up being a datastep, a proc freq and possibly a join (if you want to assign special "labels" for each string).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎06-28-2017 09:01 AM
Super User
Posts: 11,130

Re: How to create a table to list macro N by using proc sql?

The %put function has options to list the macro variables in a current scope:

 

%put _user_;

will display ALL user defined variables

 

 

Putting much data into macro variables is often a path to madness.

Super User
Posts: 19,145

Re: How to create a table to list macro N by using proc sql?

See SASHELP.VMACRO table.

 

 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 120 views
  • 1 like
  • 6 in conversation