BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Reader587
Calcite | Level 5

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Reader587
Calcite | Level 5
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
Diamond | Level 26

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

--
Paige Miller
Astounding
PROC Star

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
Calcite | Level 5

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

ballardw
Super User

@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
Calcite | Level 5

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

Reeza
Super User

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
Super User

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
Super User Tom
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1653 views
  • 1 like
  • 6 in conversation