Do Loop- Proc Freq

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Do Loop- Proc Freq

Hello everyone,

I am using the following code to run proc freq on selected variables from a dataset.  Is there a way to simplify the code with a do loop or a macro where the program itself selects every third variable starting from the fifth variable in my dataset? As in, to start from the fifth column (Changeinforce) and then select every third column from there on and run proc freq on them.  Any help is much appreciated, thank you in advance!

proc format;

value firstfmt

-100 - -60 = 'Decreased more than 60%'

-60 - -40 = '-40% to -60%'

-40 - -20 = '-20% to -40%'

-20 - 0 = '0% to -20%'

0 - 20 = '0% to 20%'

20 - 40 = '20% to 40%'

40 - 60 = '40% to 60%'

60 - 80 = '60% to 80%'

80 - 100 = '80% to 100%';

run;

proc freq data=work.test;

table changeinforce ChangeIssued Changeconsid ChangeReserve ChangeUWpol ChangeUWValue changeuwamt/nocum nopercent;

format changeinforce--changeuwamt firstfmt.;

run;


Accepted Solutions
Solution
‎09-17-2013 01:48 PM
Super User
Posts: 19,770

Re: Do Loop- Proc Freq

I would look at the table in sashelp.vcolumns and get the column names from there into a macro variable, once you've determined your numbering convention or some rule that lets you extract them.

Basically need to figure out the logic for the varnum now to get what you need. If its a fixed number of columns its easier to deal with.

For example you want columns, 5, 8, 11, 14 etc.... Is there a max number of columns to be expected or will that change as well?

proc sql;

    select name into :var_list separated by " "

    from sashelp.vcolumn

    where libname=upcase(my_libname) and memname=upcase(mydatasetname)

    and varnum in (5, 8, 11, 14 );

quit;

proc freq data=my_libname.mydatasetname;

tables &varlist/nocum nopercent;

format &varlist firstfmt. ;

run;

EDIT: Try mod(varnum-5, 3)=2 instead of varnum in (5, 8, 11, 14)....

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Do Loop- Proc Freq

Is there any naming convention to the variables, for example do they all start with change?

If so try:

proc freq data=work.test;

table change: /nocum nopercent;

format changeinforce--changeuwamt firstfmt.;

run;

Occasional Contributor
Posts: 7

Re: Do Loop- Proc Freq

Thank you for your help Reeza.  Variables do all start with change, but I named that way so that I can have an easier time running proc freq on them.  In future they might not be the same name, but they will always be the third column starting from column 5 as 1.  I am assuming a do loop, i.e. %do = 5 %by 3, or some variation of that might help?

Solution
‎09-17-2013 01:48 PM
Super User
Posts: 19,770

Re: Do Loop- Proc Freq

I would look at the table in sashelp.vcolumns and get the column names from there into a macro variable, once you've determined your numbering convention or some rule that lets you extract them.

Basically need to figure out the logic for the varnum now to get what you need. If its a fixed number of columns its easier to deal with.

For example you want columns, 5, 8, 11, 14 etc.... Is there a max number of columns to be expected or will that change as well?

proc sql;

    select name into :var_list separated by " "

    from sashelp.vcolumn

    where libname=upcase(my_libname) and memname=upcase(mydatasetname)

    and varnum in (5, 8, 11, 14 );

quit;

proc freq data=my_libname.mydatasetname;

tables &varlist/nocum nopercent;

format &varlist firstfmt. ;

run;

EDIT: Try mod(varnum-5, 3)=2 instead of varnum in (5, 8, 11, 14)....

Super Contributor
Posts: 334

Re: Do Loop- Proc Freq

I got the following to work with the Cars sashelp table using approach above. I did have to adjust the mod formula slightly:

proc sql noprint;

  select name into :vlist separated by ' '

  from dictionary.columns

  where upcase(libname)="SASHELP" and upcase(memNAME)="CARS" and mod(varnum-3,3)=2

  ;

quit;

%put &vlist;

Eric

Occasional Contributor
Posts: 7

Re: Do Loop- Proc Freq

This is very helpful,  thanks Eric!

Occasional Contributor
Posts: 7

Re: Do Loop- Proc Freq

  Thank you for your help Reeza, your approach made sense and helped out a lot. The numbers of columns will stay the same at 23, only names might change.

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 1423 views
  • 6 likes
  • 3 in conversation