topic Re: index score for occupations summarizing a multiple variable scores in SAS Programming

index score for occupations summarizing a multiple variable scores

Diana_AdventuresinSAS — Mon, 19 Mar 2018 03:42:02 GMT

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

Re: index score for occupations summarizing a multiple variable scores

Kurt_Bremser — Mon, 19 Mar 2018 05:40:20 GMT

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.

Re: index score for occupations summarizing a multiple variable scores

ballardw — Mon, 19 Mar 2018 14:34:53 GMT

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;

Re: index score for occupations summarizing a multiple variable scores

Diana_AdventuresinSAS — Tue, 20 Mar 2018 04:40:38 GMT

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

Re: index score for occupations summarizing a multiple variable scores

ballardw — Tue, 20 Mar 2018 19:45:13 GMT

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;

Re: index score for occupations summarizing a multiple variable scores

Diana_AdventuresinSAS — Wed, 21 Mar 2018 04:39:52 GMT

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

Diana