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

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

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

 

View solution in original post

9 REPLIES 9
Astounding
PROC Star

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
Calcite | Level 5
Thanks for your reply, Astounding. I don't think this works
Patrick
Opal | Level 21

"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
Calcite | Level 5
my orginal data has 35 variables
ballardw
Super User

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

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

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

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

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

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
  • 9 replies
  • 1743 views
  • 0 likes
  • 5 in conversation