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

Hello all! I have just started a new job and have been teaching myself to use SAS. Progress is slow since I have very little experience with programming languages. I would be incredibly appreciative of any help with this problem!

I have a large raw macro data set. Based on this raw data, a former co-worker used SAS to make an Excel spreadsheet. In the spreadsheet, there are 3 columns: Group, Subgroup, and the Number of Observations of Subgroup (i.e. the number of times the subgroup was observed in the data). Specifically, the Groups are things like “Administrative Services”, and the Subgroups include specific jobs like “Receptionists”, “Assistants”, and “Managers”. Below I have made an example of what the table looks like. In reality, there aren’t just 2 Groups, there are hundreds!

I have imported the Excel data into SAS. My question is: How can I make the “Share of Group” column in SAS?


Group



Subgroup



Number of Observations



Share of Group



Administrative Services



Receptionists



5



41.6%



Assistants



4



33.3%



Managers



3



25%

Computer Science



Data Analysts



10



71.4%



IT Support



4



  28.5%

What I would like to do is the following: For each Subgroup, calculate its percentage share of the entire group based on the Number of Observations. For example, I would like to be able to calculate a new variable that will show that Receptionists were 41.6% of all observed Administrative Services employees, Assistants were 33.3%, etc.

Since I have a large number of Groups, I am guessing that the best way to do this for each Group is to make a macro, but I’m not sure what to tell SAS to do in the macro.

Thank you so much for reading this! I really appreciate any help or tips I can get.

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

I think this is what you are looking for.  Two sql queries will get the job done.  Run this:

data have;

infile cards dsd dlm=',';

length group subgroup $15.;

input group $ subgroup $ num_obs;

cards;

Admin,Receptionists,5

Admin,Assistants,4

Admin,Managers,3

CompSci,DataAnalysts,10

CompSci,ITSupport,4

;

run;

proc sql;

create table want as

select group,subgroup,num_obs/tot_obs as share format=percent7.1

from(select *,sum(num_obs) as tot_obs

from have

group by group);

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

PROC FREQ, with BY GROUP; with the OUT= option will give you this information

--
Paige Miller
ballardw
Super User

proc tabulate data=have;

class group subgroup;

table group*subgroup, n pctn<subgroup>;

run;

Will make a summary table directly similar to what you want.

grande44
Calcite | Level 5

Thanks so much for your reply! Unfortunately this didn't work. SAS counted how many times the name of the Subgroup appeared in the Group, and did not use the Number of Observations variable instead. So my table ended up looking like

Receptionists 1 0.33

Assistants 1 0.33

Managers 1 0.33

Ksharp
Super User

I like ballardw 's code. Question is what form of data you would like ? report or table ?


data have;
infile cards dsd dlm=',';
length group subgroup $15.;
input group $ subgroup $ num_obs;
cards;
Admin,Receptionists,5
Admin,Assistants,4
Admin,Managers,3
CompSci,DataAnalysts,10
CompSci,ITSupport,4
;
run;
proc tabulate data=have;
class group subgroup;
var num_obs;
table group*subgroup, num_obs*(sum pctsum);
run;

Xia Keshan

Steelers_In_DC
Barite | Level 11

I think this is what you are looking for.  Two sql queries will get the job done.  Run this:

data have;

infile cards dsd dlm=',';

length group subgroup $15.;

input group $ subgroup $ num_obs;

cards;

Admin,Receptionists,5

Admin,Assistants,4

Admin,Managers,3

CompSci,DataAnalysts,10

CompSci,ITSupport,4

;

run;

proc sql;

create table want as

select group,subgroup,num_obs/tot_obs as share format=percent7.1

from(select *,sum(num_obs) as tot_obs

from have

group by group);

grande44
Calcite | Level 5

Thank you so much! You, sir, have just made my day so much easier.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 20955 views
  • 1 like
  • 5 in conversation