Hi,
I have data for 2 different years - 2008 and 2016 - on whether people living in a particular community use different substances such as alcohol, tobacco etc (yes/no variable coded as 0=No, 1=Yes).
Have (raw data):
data WORK.RAW_DATA(label='raw_data dataset written by Stat/Transfer Ver. 11.2.2106.0521 '); infile datalines dsd truncover; input Community_name:$1. Year:32. Alcohol_use:32. Tobacco_use:32. Marijuana_use:32.; datalines4; A,2008,1,1,0 A,2008,1,1,1 A,2008,1,0,1 A,2008,1,1,1 A,2008,1,1,1 A,2008,0,0,1 A,2008,0,0,1 A,2008,1,0,0 A,2008,0,1,0 A,2008,1,1,1 A,2008,0,0,1 A,2008,0,1,1 A,2008,0,1,1 A,2008,0,0,0 A,2008,1,1,1 A,2008,1,1,0 A,2008,1,1,1 A,2008,0,0,0 A,2008,0,1,1 A,2008,1,1,1 A,2016,1,0,0 A,2016,1,0,1 A,2016,0,0,0 A,2016,0,0,0 A,2016,1,1,0 A,2016,1,1,1 A,2016,1,1,1 A,2016,0,0,0 A,2016,0,0,0 A,2016,0,1,0 A,2016,0,0,1 A,2016,1,1,1 A,2016,0,1,1 A,2016,0,1,1 A,2016,0,0,0 A,2016,1,1,1 A,2016,0,0,0 A,2016,1,1,1 A,2016,0,0,0 A,2016,1,1,1 A,2016,1,0,1 A,2016,0,1,0 A,2016,0,0,0 A,2016,0,0,0 A,2016,0,1,0 ;;;;
I am using chi-square to analyze the data for each community using the following code in SAS 9.4:
proc freq data=raw_data;
table year * alcohol_use year*tobacco_use year*marijuana_use/ nopercent nocolumn chisq;
run;
The results are attached.
I am interested in the freq count and row percentages for the yes responses coded "1" in 2008 and 2016, and predicted probability of chi-square value (highlighted in the resutls). Is there a way to export just the highlighted results to an excel file instead of having to manually enter the numbers or copy/paste for all the outcomes?
Want (summary statistics):
Outcome |
2008count |
2008percent |
2016count |
2016percent |
prob |
Alcohol_use |
12 |
57.14 |
10 |
40.00 |
0.2463 |
Tobacco_use |
14 |
66.67 |
12 |
48.00 |
0.2033 |
Marijuana_use |
15 |
71.43 |
11 |
44.00 |
0.0616 |
Any suggestions?
Thank you.
There are two parts to your question.
First, how to create a dataset that contains what you want. The following will work with your example data:
proc freq data=raw_data; table year * alcohol_use year*tobacco_use year*marijuana_use/ nopercent nocol chisq; ods output CrossTabFreqs=freq; ods output ChiSq=chi; run; data want (keep=outcome year count: percent:); set freq (where=(_TYPE_ eq '11' and (alcohol_use eq 1 or tobacco_use eq 1 or marijuana_use eq 1) and year in (2008,2016))); length outcome $12; retain count2008 percent2008; outcome=substr(table,14); if year eq 2008 then do; count2008=frequency; percent2008=rowpercent; end; else do; count2016=frequency; percent2016=rowpercent; output; end; run; data want (drop=statistic); set want; set chi (where=(statistic eq 'Chi-Square') keep=statistic prob); run;
You can use a number of ways to get that into excel, e.g., proc export.
Art, CEO, AnalystFinder.com
Please provide example data in the form of a datastep if you would like example code. If you don't know how to create such a datastep Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
There are two parts to your question.
First, how to create a dataset that contains what you want. The following will work with your example data:
proc freq data=raw_data; table year * alcohol_use year*tobacco_use year*marijuana_use/ nopercent nocol chisq; ods output CrossTabFreqs=freq; ods output ChiSq=chi; run; data want (keep=outcome year count: percent:); set freq (where=(_TYPE_ eq '11' and (alcohol_use eq 1 or tobacco_use eq 1 or marijuana_use eq 1) and year in (2008,2016))); length outcome $12; retain count2008 percent2008; outcome=substr(table,14); if year eq 2008 then do; count2008=frequency; percent2008=rowpercent; end; else do; count2016=frequency; percent2016=rowpercent; output; end; run; data want (drop=statistic); set want; set chi (where=(statistic eq 'Chi-Square') keep=statistic prob); run;
You can use a number of ways to get that into excel, e.g., proc export.
Art, CEO, AnalystFinder.com
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.