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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
Kurt_Bremser
Super User

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
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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

 

Diana

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
  • 5 replies
  • 613 views
  • 0 likes
  • 3 in conversation