BookmarkSubscribeRSS Feed
nguyek13
Calcite | Level 5

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; 
5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11
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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 5 replies
  • 1590 views
  • 1 like
  • 4 in conversation