turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Proc freq and Transpose

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-12-2012 11:16 AM

Hi Team,

After a 2 way frequency of code and type variables

TYPE can take the values of C N N/A or Q

Ex:536 and 537 codes do not have Q

code type count percent

532 C 32 5.97

532 N 34 6.34

532 n/a 305 56.90

532 Q 4 0.74

534 C 1 0.18

534 N 3 0.55

534 N/A 28 5.22

534 Q 1 0.18

535 C 3 0.55

535 N 4 0.74

535 N/A 51 9.51

536 C 4 0.74

536 N 1 0.18

536 N/A 48 8.95

537 C 1 0.18

537 N 1 0.18

537 N/A 15 2.79

I want like shown below but what percentage comes under percent and how to get it there.????????????

I was told that around 20 percent comes in the first row. How can I get like that...

Also how to get the total variable.....

Thanks

C Q n n/a code total percent

32 4 34 303 532 373 ?????

Thanks so much

Accepted Solutions

Solution

10-12-2012
04:51 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 04:51 PM

Then I guess you probably need two passes:

**data** want;

set have (in=up) have;

if up then sum+total;

if ^up then do;

percent=total/sum;

output;

end;

drop sum;

**run**;

Haikuo

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 01:47 PM

I think you have know what percent comes under percent. Do you want it to be the total/grand total for example? Percent is such a bad label, consider using a different term if possible, ie percent of grand total, row percent...

Given the format I'd expect it to be total/grand total for a standard table, but it really depends on what you're doing. From the data it looks like if it was row percent it would be 70% so not sure where the 20% is coming from.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-12-2012 03:14 PM

Couple of ways, but in your original output from proc freq you had the percent as well.

If you transpose that variable and then add it up similar to above you can merge it in.

Or you can add in the grand total and divide by that using a variety of methods.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-12-2012 03:31 PM

Hi,

Actually what you see above is from several datasets combined. I did it in bits because I wanted identification of each cohort. To give labels and so on in the end. So I guess Percent comin from the proc freq and transpose steps will not work out for me.

Adding the grand total????How can I do that???

Based on the codes I categorized :

734-757 form a dataset

830 and 847 form a dataset

5832-5839 form a dataset

5877-5879 adataset

9342 a dataset

9357-9366 a dataset

Set them all finally

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 03:40 PM

This will work then....

proc sql;

create table want as

select *, totals/sum(totals)

from have;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-12-2012 03:53 PM

Hi

Thanks for the help.

I tried this and its not working. A percent variable is created but i get 100 for all the rows!!!1

data fin;

set cohort_final;

percent=Totals/sum(Totals)*100;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 04:10 PM

That's not proc sql that's a datastep which operates differently.

In Proc SQL, the sum() function adds values over the column, in a datastep it works over a row so 100% makes sense in a datastep.

proc sql;

create table want as

select *, totals/sum(totals) as percent

from have;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-12-2012 04:13 PM

Got you,

Could you also help me with the data step. I mean how can i get the same thing with Data step

Thanks

Solution

10-12-2012
04:51 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 04:51 PM

Then I guess you probably need two passes:

**data** want;

set have (in=up) have;

if up then sum+total;

if ^up then do;

percent=total/sum;

output;

end;

drop sum;

**run**;

Haikuo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Haikuo

10-12-2012 04:58 PM

Hi Hai,

I got the answer.

But could you please explain to me what is two passes. and whats going on with the code you sent..

Also why are we setting the **have** dataset** twice**

Thanks a ton

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 05:16 PM

"Set have (in=up) have;" is to stack two identical table one after another. It is similar to what 'proc append" does, but with lower efficiency and more control. So we can get the sum in the first 'have', and apply it on the second one, and you want to make sure only the second one get to output.

Haikuo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Haikuo

10-12-2012 05:24 PM

Great Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to robertrao

10-12-2012 05:23 PM

The most basic and beginner method is to create the total in proc means and then merge it in. You should really use proc sql though, both of these methods are more inefficient in terms of your time.

proc means data=sashelp.class;

output out=grand_total sum(height)=grand_total;

run;

data class;

set sashelp.class;

if _n_=1 then set grand_total; *merge in total across all rows;

percent=height/grand_total;

format percent percent10.1;

run;