Do loops Proc sql

[Reader587]

Hello All,

I want to create tables using proc sql. I am looking to write a do loop that will make my job easier. I want to write code that will limit my code/job posted below. The data set is from sashelp.cars.  Additionally, is there a way to use proc freq output out to create a table with nlevels then use the nlevels table to create the values for the macro? 

Thanks. 

proc sql;
select count(distinct make)
into:cm from &c;
select distinct make
into: make1- from &c;
select count(distinct type)
into: ct from &c;
select distinct type
into:type1- from &c;
select count(distinct origin)
into: co from &c;
select distinct origin
into: origin1- from &c;
select count(distinct drivetrain)
into:cdt from &c;
select distinct drivetrain
into:dt1-
from &c;
quit;

[PaigeMiller]

Not clear to me how a loop would work here, but perhaps this is helpful

%macro dothis(dsn=,variable=,countname=);
    proc sql;
        select count(distinct &variable) into &countname from &dsn;
        select distinct &variable into : &variable.1- from &dsn;
    quit;
%mend;

%dothis(dsn=sashelp.cars,variable=make,countname=cm)

You could use PROC FREQ output to get all of the variable names and variable levels, but I don't really see why you are asking about that in relation to the other part of the question.

[Reader587]

I need the count(distinct &variable) to be a macro.
For example, when you call the macro, the parameter countname=cm. However, I do not want to have to put in a number.

[PaigeMiller]

---

@Reader587 wrote:
I need the count(distinct &variable) to be a macro.
For example, when you call the macro, the parameter countname=cm. However, I do not want to have to put in a number.

---

So terminology is important to convey clear ideas. The count cannot be a macro. It might be useful to make count a macro variable (macros are not the same as macro variables), but the question remains ... why? People are asking why — many times the original question assumes a macro variable (or a macro) is the way to get to the next step, when in fact often macros and macro variables are not needed. But we need to know what you intend to do next, and this requires explaining the problem at some higher level. We're not asking about coding this in SAS, we're asking about what you are doing, and what are the next steps (table, report, graph, powerpoint, excel file, etc.)

[Astounding]

I agree, with @PaigeMiller that your objective isn't clear.  Why not use a simple program with no macro language?

proc freq data=sashelp.cars noprint;
   tables make / out=makes;
   tables type / out=types;
   tables origin / out=origins;
   tables drivetrain / out=drivetrains;
run;

What is the reason for complicating any of this with macro variables?  Is there some secret "next step" that would benefit from macro variables?  (That next step might also be possible without any macro language, but we would need to know what it is.)

[Reader587]

The next step is to create a table for all values of make, origin, and etc...

[ballardw]

---

@Reader587 wrote:

The next step is to create a table for all values of make, origin, and etc...

---

What sort of table? You can do that without macro variables just using select distinct with create table.

[Reader587]

But I wouldn't want to do those 35 times. 

[Reeza]

Those 35 tables are one for each unique category or combinations of categories?

This is your method of approaching the problem, but there are other, more optimal ways. 

Unfortunately your method is complicated and difficult to scale well, which is why others are asking for the ultimate goal. Also this question is commonly asked, and 90% of the time, a user doesn't realize there's an easier, more efficient way to do this type of processing in SAS. 

https://xyproblem.info/

The XY problem is asking about your attempted solution rather than your actual problem. This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

• User wants to do X.
• User doesn't know how to do X, but thinks they can fumble their way to a solution if they can just manage to do Y.
• User doesn't know how to do Y either.
• User asks for help with Y.
• Others try to help user with Y, but are confused because Y seems like a strange problem to want to solve.
• After much interaction and wasted time, it finally becomes clear that the user really wants help with X, and that Y wasn't even a suitable solution for X.

The problem occurs when people get stuck on what they believe is the solution and are unable step back and explain the issue in full.

[ballardw]

Pick a smaller data set, perhaps SASHELP.CLASS which only has 5 variables and 19 observations.

Show us what the result from that would look like. Not how to do it. Not any code. Just the result BUT we need to know the contents of the data set you start with.

Maybe this will give you some hints.

proc freq data=sashelp.cars;
  ods output onewayfreqs=anexample;
  tables make type origin drivetrain;
run;

data nicer;
   set anexample;
   Varname= scan(table,2);
   varvalue = cats(of f_:);
   keep varname varvalue frequency;
run;

[Tom · Accepted solution]

No idea what you are trying to do but the posted code is inefficient.

You appear to running the same query twice just so you can get the COUNT, which SQL will generate automatically for you if you want it ONCE.

You have:

select count(distinct make) into :cm from &c;
select distinct make into :make1- from &c;

Which you can replace with

select distinct make into :make1- from &c;
%let cm=&sqlobs;

If you want to parameterize that then it would help if you used the same string to generate all of the macro variable names.

%let dsname=sashelp.cars;
%let varname=make ;
proc sql noprint;
select distinct &varname into :&varname._1- from &dsname;
%let &varname._n=&sqlobs;
quit;

Results

935  options symbolgen;
936  proc sql noprint;
937  select distinct &varname into :&varname._1- from &dsname;
SYMBOLGEN:  Macro variable VARNAME resolves to make
SYMBOLGEN:  Macro variable VARNAME resolves to make
SYMBOLGEN:  Macro variable DSNAME resolves to sashelp.cars
938  %let &varname._n=&sqlobs;
SYMBOLGEN:  Macro variable VARNAME resolves to make
SYMBOLGEN:  Macro variable SQLOBS resolves to 38
939  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


940  %put &=make_1 &=make_38 ;
SYMBOLGEN:  Macro variable MAKE_1 resolves to Acura
SYMBOLGEN:  Macro variable MAKE_38 resolves to Volvo
MAKE_1=Acura MAKE_38=Volvo