index score for occupations summarizing a multiple variable scores

[Diana_AdventuresinSAS]

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

[Kurt_Bremser]

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.

[ballardw]

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;

[Diana_AdventuresinSAS]

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 😄

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

[ballardw · Accepted solution]

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;

[Diana_AdventuresinSAS]

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

Diana