topic How to create a table to list macro N by using proc sql? in SAS Programming

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

ybz12003 — Mon, 26 Jun 2017 12:37:29 GMT

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

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

;

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

N1 N2

3 2

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

PaigeMiller — Mon, 26 Jun 2017 12:10:49 GMT

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.

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

RW9 — Mon, 26 Jun 2017 12:09:17 GMT

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.

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

Kurt_Bremser — Mon, 26 Jun 2017 12:13:09 GMT

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

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

ballardw — Mon, 26 Jun 2017 14:25:59 GMT

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.

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

Reeza — Mon, 26 Jun 2017 14:44:12 GMT

See SASHELP.VMACRO table.