index score for occupations summarizing a multiple variable scores

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

index score for occupations summarizing a multiple variable scores

Hello!

 

My apologies for the vague subject line. I'm working with hundreds of occupations. Attached is a screen shot of the data. Each occupation is scored on a number of abilities the worker needs (e.g. speech clarity). There are two scales: Importance and Level. I only care about Importance. I want to sum all the scores of Importance for specific abilities (21 of them so I won't list them here); each ability is assigned an Element ID but it's shared by both Importance and Level. The sum will end up as an index I will call Cognitive Index. I want to output this into a new permanent data set - let's call it CogAbilities. The new set will have 2 variables and will look like this:

 

OCCSOC      CognitiveIndex

11-1011.00        80

11-1011.03        91

so on

 

How do I write that program? The fact that each occupation has 105 rows of data is most challenging to me.

 

Thanks!!!

Diana


Accepted Solutions
Solution
‎03-21-2018 12:37 AM
Super User
Posts: 13,525

Re: index score for occupations summarizing a multiple variable scores

Posted in reply to Diana_AdventuresinSAS

Then add the ElementId to the where statement to subset the data to just those records:

This example would only sum when the elementid is one of the first 3. Finish adding the remainder in the list.

 

If the list of the elementids NOT to use is shorter it may be easier to use this code:   and elementid not in ('1.A.1.a.5' 1.A.1.a.6' ) and continue to list all of the ones you don't want.

 

Proc summary data=yourdataset nway;

   where scalename='Importance'

             and elementid in ('1.A.1.a.1' '1.A.1.a.2' '1.A.1.a.3')

   ;

    class OCCSOC;

    var datavalue;

   output out=want (drop=_type_ _freq_) sum= cognitiveindex;

run;

View solution in original post


All Replies
Super User
Posts: 10,238

Re: index score for occupations summarizing a multiple variable scores

Posted in reply to Diana_AdventuresinSAS

Please supply example data in a data step with datalines and post the code you already have. Use the code posting buttons ({i} and "little running man"), do not use MS Office files.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,525

Re: index score for occupations summarizing a multiple variable scores

Posted in reply to Diana_AdventuresinSAS

Having actual data is helpful but I would guess you are looking for something like:

 

Proc summary data=yourdataset nway;

   where scalename='Importance';

    class OCCSOC;

    var datavalue;

   output out=want (drop=_type_ _freq_) sum= cognitiveindex;

run;

Occasional Contributor
Posts: 16

Re: index score for occupations summarizing a multiple variable scores

Hello and thank you for your reply! I attached the excel data file (when I tried to attach the sas data file it said "The contents of the attachment doesn't match its file type" I hope this works anyway). You may notice some of the variables were deleted and/or renamed but everything you need is there. Your code is almost what I need Smiley Very Happy

 

Here's the program I used:

 

Proc summary data=onet.abilities nway;
where scalename='Importance';
  class OCCSOC;
  var score;
output out=cognitivedata (drop=_type_ _freq_) sum= cognitiveindex;
run;

 

If you notice that variable "ElementID", I specifically need the following elements to sum the cognitive abilities index:

 

1.A.1.a.1

1.A.1.a.2

1.A.1.a.3

1.A.1.a.4

1.A.1.b.1

1.A.1.b.2

1.A.1.b.3

1.A.1.b.4

1.A.1.b.5

1.A.1.b.6

1.A.1.b.7

1.A.1.c.1

1.A.1.c.2

1.A.1.d.1

1.A.1.e.1

1.A.1.e.2

1.A.1.e.3

1.A.1.f.1

1.A.1.f.2

1.A.1.g.1

1.A.1.g.2

 

I believe your code sums all of the elements per occupation. Assuming that, I went ahead and printed the first 10 observations, and the index for the first occupation does not equal to the sum of all of its the elements. 

 

If you have any other suggestions to fix this and narrow the code to those specific 21 element IDs, I would greatly appreciate it!

 

Thank you!!

Diana

 

 

Solution
‎03-21-2018 12:37 AM
Super User
Posts: 13,525

Re: index score for occupations summarizing a multiple variable scores

Posted in reply to Diana_AdventuresinSAS

Then add the ElementId to the where statement to subset the data to just those records:

This example would only sum when the elementid is one of the first 3. Finish adding the remainder in the list.

 

If the list of the elementids NOT to use is shorter it may be easier to use this code:   and elementid not in ('1.A.1.a.5' 1.A.1.a.6' ) and continue to list all of the ones you don't want.

 

Proc summary data=yourdataset nway;

   where scalename='Importance'

             and elementid in ('1.A.1.a.1' '1.A.1.a.2' '1.A.1.a.3')

   ;

    class OCCSOC;

    var datavalue;

   output out=want (drop=_type_ _freq_) sum= cognitiveindex;

run;

Occasional Contributor
Posts: 16

Re: index score for occupations summarizing a multiple variable scores

Thank you so much!! It worked beautifully. You are amazing. You made a huge difference in this grad student's life. God bless!!

 

Diana

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 140 views
  • 0 likes
  • 3 in conversation