Proc Freq automatically name output multiple tables

[Anna_DY]

I have a simulated dataset with 35 variables (_65, _66,.....,_99) referencing age groups. I'd like to count how many zeros in the data for each age group and get a summary table, so  I tried "proc format" as below, however, I don't know how to specify the output files names (after Out 😃 :  My original thought was to use data step to join all the 35 tables. 

Specifically, my questions are 1) can anyone provide a better way to do this? I guess maybe macro or loop can do this  or, 2) follow my current code, to give output dataset names.

Please advise. Thanks!

proc format;
value fmt
0 = 'Negative'
other = 'Positive';
run;
proc freq data=initial4_64;
table _65-_99 /out = ;
format _65-_99 fmt.;
run;

[Astounding]

Here's an earlier question that is similar enough, to point you in the right direction:

https://communities.sas.com/t5/SAS-Programming/How-to-save-proc-freq-result-as-a-table/m-p/347589

[Anna_DY]

Thanks for your reply, Astounding. I don't think this works

[Patrick]

"I have a simulated dataset with 35 variables.... My original thought was to use data step to join all the 35 tables"

One table with 35 variables or 35 tables with one variable?

[Anna_DY]

my orginal data has 35 variables

[ballardw]

The older syntax using out= on the tables statement will only send ONE variable to the output set.

Pray tell, what does a 0 for a variable such as you describe actually mean in terms of analysis? I have a strong suspicion that you have made a choice somewhere that is going to complicate almost any sort of analysis based on ages. And what will you do with 35 data sets of these counts?

[Anna_DY]

Thanks for your reply! 

In dact I originally tried to run followign code, however, I have 35 variablee (age groups), I was hoping there is a simple way that I could avoid typing these 35 times
/***count how many of zeros  (actually negative which I have recoded as zeros) for each year****/
proc sql; create table sum_table
as select distinct sum(case when _66<=0 then 1 else 0 end ) as Neg_66,
sum (case when _67=0 then 1 else 0 end) as Neg_67,
sum (case when _68=0 then 1 else 0 end ) as Neg_68,

sum (case when _99=0 then 1 else 0 end ) as Neg_99

from initial3_64;

[ballardw]

Lets go back a step, or maybe more than one.

Describe what your original data looked like before you re-coded anything or possibly even created 35 variables. Better would be to provide a small example of the data , say 10 to 30 records.

Then tell us the analysis or question that you are attempting to answer.

This whole bit of creating 35 variables and then wanting 35 data sets is symptomatic of inexperience and perhaps it would help you more to consider choices made earlier. It is not uncommon for new(ish) programmers to attempt to force one or two tools to do everything and get part way through a problem and encounter something that has become awkward to proceed with, such as creating 35 data sets, one for each variable.

[Tom · Accepted solution]

---

@Anna_DY wrote:

Thanks for your reply! 

In dact I originally tried to run followign code, however, I have 35 variablee (age groups), I was hoping there is a simple way that I could avoid typing these 35 times
/***count how many of zeros  (actually negative which I have recoded as zeros) for each year****/
proc sql; create table sum_table
as select distinct sum(case when _66<=0 then 1 else 0 end ) as Neg_66,
sum (case when _67=0 then 1 else 0 end) as Neg_67,
sum (case when _68=0 then 1 else 0 end ) as Neg_68,

sum (case when _99=0 then 1 else 0 end ) as Neg_99

from initial3_64;

---

Sounds like you started with two variables. AGE and some YES/NO or Boolean variable, lets call it NEG to match you variable prefix.  In that case make a FORMAT to categorize the AGE into your 35 groups, let's call it AGEGRP.  Then use it with PROC FREQ.

proc format;
value agegrp
  99 = 'missing'
  0-5 = 'infant'
  6-12 = 'pre-teen'
  12-19 = 'teen'
  20-64= 'adult'
  65-98,100-high = 'elderly'
;
run;
proc freq data=original
  tables age*neg ;
  format age agegrp.;
run;

[Anna_DY]

Thanks, Tom. I think this will work. I just need to transpose my data.