Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Proc Freq automatically name output multiple tables

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-11-2023 07:46 PM
(224 views)

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;

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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;
```

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

*"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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

my orginal data has 35 variables

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

SAS is headed **back** to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.

**Interested in speaking?** Content from our attendees is one of the reasons that makes SAS Innovate such a special event!

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.