Help can't figure out to get percent of usage of aspirin from data set

[nguyek13]

I'm trying to get the percent from the yes column of the total from each group and I cannot figure out why I'm receiving errors? What I tried to do before but I know there is a section missing to get the percentage as well.

data aspirin; 
	infile '/folders/myfolders/Aspirin Use DATA .xlsx';
	input Age Gender Race Use Percent;
proc format; 
	value agegroup   18 -< 64 = '18-64';
				 65   = '65';
	value $fmt'w'='White'
				'B'= 'Black'
				'H'= 'Hispanic'
				'O'= 'Other';
proc print data=aspirin;
	format Age. Gender. Race. Use.;
Title 'Aspirin Use';
run; 

[RW9]

Please post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Your code makes no sense:

data aspirin; infile '/folders/myfolders/Aspirin Use DATA .xlsx'; input Age Gender Race Use Percent;

You do not read in data from an Excel file like that.

No mention of percent has been made in that code, or attempt calculate percent?

 Do ensure you finish code block with the relevant item, e.g. run;

Now if you get your data in something like:

proc sql;
  create table want as 
  select  age,
          sex,
          race,
          count(*) as num_items,
          sum(case when aspirin="yes" then 1 else 0 end) as num_yes,
          (calculated num_items/calculated num_yes) * 100 as percent
  from    have
  group by age,
           sex,
           race;
quit;

Or proc freq.  Or maybe means or summary as well.

[ballardw]

---

@nguyek13 wrote:

I'm trying to get the percent from the yes column of the total from each group and I cannot figure out why I'm receiving errors? What I tried to do before but I know there is a section missing to get the percentage as well.

data aspirin; 
	infile '/folders/myfolders/Aspirin Use DATA .xlsx';
	input Age Gender Race Use Percent;
proc format; 
	value agegroup   18 -< 64 = '18-64';
				 65   = '65';
	value $fmt'w'='White'
				'B'= 'Black'
				'H'= 'Hispanic'
				'O'= 'Other';
proc print data=aspirin;
	format Age. Gender. Race. Use.;
Title 'Aspirin Use';
run; 

---

What "yes column"? You do not show a "column" named Yes in the code. I suspect you mean the USE column.

Show the log results with the error messages: Copy the text of the submitted code and any error or warning messages from the log and paste into a code box opened using the forum's {I} icon to preserve message formatting.

I suspect that the first error is "file not found" due to the space before the .xlsx.

Your syntax for applying formats is incorrect. The statement is Format <list of variable names> <name of format to apply>;

such as Format age agegroup. ;

Your format definition of $fmt is going to have an issue with the actual data due to

	value $fmt'w'='White'

as the actual value in the spreadsheet is 'W' not 'w'.

I would say 1) save the data to CSV file format using the File Save AS in Excel. 2) read the csv as delimited with something like:

data aspirin; 
   infile '/folders/myfolders/Aspirin Use DATA.csv' dlm=',';
   input Age Gender $ Race $ Use $ ;
run;

The dollar signs after the variable names indicate the values are expected to be character such as Female or Male, W B H O yes no.

Then perhaps

proc freq data=have;
  tables (age gender race)*use;
  format age agegroup. race $fmt.;
run;

The (age gender race)*use in proc freq on the tables statement says compare each variable in the parentheses with the variable Use.

BTW $fmt is a poor name choice for a format. $race. would make much more sense.

[nguyek13]

Update: 

So I tried to make the excel file into csv but that didn't work too well, it gave me errors and everything was written in symbols? So i tried this and SAS is still giving me an error. From the dataset I'm trying to get the percent of yeses from the total of each age group, gender, and race. Also,  I can't seem to figure out the mean and standard deviation from this? 

data aspirin; infile '/folders/myfolders/Aspirin Use DATA .xlsx'; 
	input Age Gender Race Use Percent;
proc sql;
  create table want as 
  select  age,
          sex,
          race,
          count(*) as num_items,
          sum(case when aspirin="yes" then 1 else 0 end) as num_yes,
          (calculated num_items/calculated num_yes) * 100 as percent
  from    have
  group by age,
           sex,
           race;
quit;

[RW9]

Please take some time to run through the how to videos:

https://video.sas.com/category/videos/how-to-tutorials

And read the manual.

As I previously posted, you do not read Excel files into SAS using the method you present here.

If you want help on a question once you have the data into a SAS dataset, then post this as a text in a code window, following the post:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

So that we have some data to work with.

[error_prone]

Check th documentation! Excel files are read by either libname-statement or proc import. To convert an excel file to CSV, highly recommended, use save-as in excel and select the csv file type.