Generating Proc SQL using macro

Reply
Contributor
Posts: 60

Generating Proc SQL using macro

Hi,

I am trying to generate proc sql code using macro.below is the code.But some how I am doing syntactical mistake due which i facing an issue.can I get anyone's help to correct it.

%let var=sas base pravin;

%macro char;

     proc sql noprint;

     create table chuck as

     select

     %do i=1 %to 3;

     %let var_name=%scan(&var,&i,' ');

     as col_name,count(distinct &var_name) as cnt

     %end;

     from table_name

;

quit;

%mend char;

%char;

My final table will looks below.

Col_nameCnt
SAS10000
BASE15000
PRAVIN

Let me if you have any questions

Super User
Posts: 19,105

Re: Generating Proc SQL using macro

Your code doesn't make sense to me. What are you trying to do?

Contributor
Posts: 60

Re: Generating Proc SQL using macro

My apology. objective to find the distict count of the variable in one shot.I want to use proc sql.

let me know if it make sense now.

Super User
Posts: 19,105

Re: Generating Proc SQL using macro

What version of SAS do you have? Count distinct in proc sql isn't always correct.

50827 - When you use the COUNT(distinct x) function in the SQL procedure, incorrect counts might be ...

Something like this would work, though I'd recommend proc freq and nlevels instead see below. Then something like your original code could be used instead.

%macro char(var=, n=, table=);

%do i=1 %to &n;

%let var_name=%scan(&var,&i,' ');

     proc sql noprint;

     create table chuck as

     select

     var_name

     as col_name,count(distinct &var_name) as cnt

     from &table

;

quit;

proc append base=want data=chuck force;

run;

%end;

%mend char;

%char(var=sas base pravin, n=3, table=table_name);

ods table nlevels=want;

proc freq data=sashelp.class nlevels;

table age;

table sex;

run;

Contributor
Posts: 60

Re: Generating Proc SQL using macro

Hi Reeza,

I am aware of proc sql and it was you only who has suggested in my previous discussion.Thanks once again for your input.

it's come with precision. but the field which I am using did not have precision.

I have already given a thought to run it in a loop but I don't want to do the same. as it will take more time. I am facing reource and time crunch.due to which I am thinking to run it in one shot.

Super User
Posts: 19,105

Re: Generating Proc SQL using macro

If you're looking for efficencies you should be looking at hash or DoW loops instead.

edit: if this is referring to your previous post of running out of memory issue, then its best to do one variable at a time and stack the results like my initial solution. trying to do all will cause you to run out of memory, it may take a bit longer but will process.  i'll also echo what others have said, if a field has more than 30k distinct values it probably isn't of use to know that.

Super User
Super User
Posts: 6,844

Re: Generating Proc SQL using macro

For your specific problem use PROC FREQ NLEVELS.

ods output nlevels=want ;

proc freq nlevels;

tables &var ;

run;

If you wanted to generate counts the way you have you might want let PROC TRANSPOSE do most of the work for you.

%macro char(var=, table=);

%local i sep;

proc sql ;

  create table chuck as select

%do i=1 %to %sysfunc(countw(&var));

  &sep count(distinct %scan(&var,&i)) as %scan(&var,&i)

  %let sep=,;

%end;

  from &table

;

quit;

proc transpose data=chuck out=want ;

run;

%mend char;

Super User
Posts: 9,867

Re: Generating Proc SQL using macro

proc freq would be more easy.

%let var=name sex age;

%macro char;

  ods select NLevels;

  ods output  NLevels=chuck;

     proc freq data=sashelp.class nlevels;

  tables &var;

  run;

%mend char;

%char

Xia Keshan

Ask a Question
Discussion stats
  • 7 replies
  • 312 views
  • 0 likes
  • 4 in conversation